Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 31 of 31

Thread: Changing ActiveX control properties based on cell text

  1. #21
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    Thanks snb. I commented out all the rest of the current code and subbed this in. Once the button went red, it stayed red, no matter how I changed my dropdown selections. Maybe this will work with all or part of the existing code? I'll play with it and see if I can find the happy spot.

  2. #22
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I changed my dropdown selections
    What dropdown selections, where ?

    Post a sample file.

  3. #23
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    Apparently, my response from yesterday did not post. Trying again. The code above worked, but once the button changed to red, it stayed that way, no matter what combination of inputs there were. I started wondering if maybe I could just add a third sub, similar to UnwindChart, where the selection of G7 changes the button color the same way F26 does, just with values specific to those cells. The code would then run at each change in either cell, regardless of which is selected first, or later changed. Thoughts?

  4. #24
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    All of the cells that this code runs on have in-cell list validation. Not sure I made that clear earlier in this thread, sorry. This is all part of a form that our Sales team fills out, so I've added dropdown lists to simplify their task.

    I have a dummy file to share, but adding one to a post is something I've never done, so I'm not sure how I do that. Care to educate me on that? I'll post this and search the help and, if I find some direction, maybe have another post soon.

    Thanks for your help!

  5. #25
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    I've searched around and am apparently missing something, because I can't find a way to post my sample file. I'm sure it's right out in plain sight. Any help?
    Thanks.

  6. #26
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #27
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    Thanks p45cal. I've seen that page and attempted to follow it, but I'm not seeing a Manage Attachments button anywhere. I see that I am allowed to post them, just need to know where to look.

  8. #28
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #29
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    They had it cleverly hidden under Go Advanced. Now I know.
    Attached Files Attached Files

  10. #30
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You don't need the UnwindChartPrint and UnwindChartItem macros, this by itself should do it:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("G7,F26")) Is Nothing Then    'you don't need this line but it saves on processing (paired with End If below comment too)
      With CommandButton1
        If ([G7] = "" Or [G7] = "Unprinted") And ([F26] = "Roll" Or [F26] = "Roll_Stock") Then
          .BackColor = vbRed
          .ForeColor = vbWhite
          .Font.Bold = True
        Else
          .BackColor = RGB(240, 240, 240)
          .ForeColor = vbBlack
          .Font.Bold = False
        End If
      End With
    End If    'you don't need this line but it saves on processing (paired with If comment above too)
    End Sub
    It can be shortened a la snb, but I leave it as it is for readability.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #31
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    Thanks p45cal. Earlier in the thread, I noted that I had two separate, unrelated procedures that would both require the Worksheet_Change sub. It was when I tried getting the two of them into the same sub that I derailed. Part of the solution was to create the separate Print and Item macros and have them called when the specific changes occurred. It looks like I can still use that approach, though, but without both macros. With your code, it looks like I'd be able to combine them into one, with fewer lines.

    I'll give this a try and let you know how it goes. Thanks for the help!

Posting Permissions

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