Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Formatting on demand? (XL97/WinNT4)

    I need (this time!) to either have the background/line colour of an autoshape change according to the content of another cell as this cell is changed, in the way conditional formatting would work on a cell (ie 'if C15 = "Tailored" then shape.colour = black else shape.colour = white' - not intended to be real code, of course!), or to run a macro by clicking in a cell, rather than on a shape ...

    Can someone tell if one or the other is possible, please? And give an idea of how?!

    Many thanks in advance!
    Beryl M


  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    Could you describe what effect it is you want to accomplish, because I fail to see the resemblance between both options you have given.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    The workbook has a 'master' cell with a number of options; when choosing from these options, some cause the contents a series of adjacent cells to become visible, others don't. Whether these options are visible or not is decided by conditional formatting, black text on a black background being used to 'hide' the contents when it's not needed (dangerous, I would say, but it's not my workbook, I'm just automating some functions for him!).

    He wants the button to run the macro to be visible only if the optional series is visible, hence the request for either running a macro from a cell (conditionally formatted not to be seen with the rest when not wanted) or to change the colours of a button so it is 'hidden' with the rest when the 'master' cell is changed to one of the options that doesn't use it!

    If you've got a better idea I'd really like to hear it!

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Just thought of something ... a transparent button with no text or colour at all (ie invisible) and the cell *beneath* containing the label, conditionally formatted with the rest so it only *appears* to be there when the rest is ... do you think that would work?
    Beryl M


  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    Don't worry, Jan, that works perfectly! As dangerous as the rest, of course, but if he's happy with the rest he can't complain about this one!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> anyway!
    Beryl M


  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    Why not run code on a worksheet change event to check the "options" and based on the options selected, decide if the the macro button is visible or not visible and set it accordingly?

    Steve

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    Sounds good (she says, cautiously) ... how would I go about doing that, please?
    Beryl M


  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    <<but if he's happy with the rest he can't complain about this one>>

    <g>. Glad you got it sorted.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    Do you want generic code or do you want to provide some details and conditions and more specific code?

    Steve

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    Whichever's easiest - if specific, what details do you need?

    Say sheet1.C16 is the 'master' cell (it's content changing what prompts the button to change) and if it is 'profile' the button needs to be visible, if it's 'tailored' it needs to be invisible. The button is a commandbutton called 'Run' and how the heck to do you refer to it? That's one of the things I fell over when trying to do this!

    Many thanks
    Beryl M


  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    It depends on the type of button you have used. For a button from the control toolbox, simply use the name of the button:

    Worksheets("mysheet").ButtonName.Visible=True

    If it is a button from the forms toolbar , use:

    Worksheets("mysheet").Buttons("ButtonName").Visibl e=True
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    Thanks, Jan, that clears up a big problem for me!

    Now if I could just work out what Steve meant by "...run code on a worksheet change event to check the "options" ..." I'd be well away! And to be honest, it's how to trigger the code to run that is my biggest stumbling block in this context.

    By the way, if the button is invisible, is it still usable - by which I mean, if you hover over where it is when it's visible, will the cursor still change to the hand and clicking on it will run the macro? Or does hiding it mean it's effectively not there to be clicked on?

    Ta muchly
    Beryl M


  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    Add this code the worksheet page.
    Now when you edit and change cell C16, if it is "profile" it will show the button, if it is not profile it will hide the button. I assumed the button was from the controls toolbox and named ButtonName (change as appropriate.

    Steve
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("C16")) Is Nothing Then
    If Range("C16").Value = "profile" Then
    ActiveSheet.ButtonName.Visible = True
    Else
    ActiveSheet.ButtonName.Visible = False
    End If
    End If
    End Sub

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    Wonderfubble! Ta ever so muchly.

    One quick question - when the button is not visible, is it still clickable on? By which I mean, is it literally invisible but otherwise there and available, or does making it invisible effectively remove it from the sheet for the duration?

    Many thanks
    Beryl M


  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    You can not click on an invisible button. If you use the Controls toolbox button, you could instead use .enabled = true/false, to keep them visible but unclickable (they will be "shaded out")

    Steve

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Formatting on demand? (XL97/WinNT4)

    Great, thanks, Steve - that's perfect!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •