Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 31

Thread: Changing ActiveX control properties based on cell text

  1. #1
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location

    Changing ActiveX control properties based on cell text

    I have a cell with list validation in which there are a small number of text selections. Also on the worksheet is an ActiveX button. I want two of the items in the list, when selected, to change the button face to red and the font to bold white. If they get deselected, then I want them to return to the defaults. I've tried everything I can think of, but nothing is working. I thought Application.Volatile would do the trick, but still nothing. From what I can see, it is not recognizing that there is anything in the cell. It's actually several cells merged together, which I also tried in the code to no avail. I know there's an answer and am obviously missing something. Any ideas?

    Thanks!

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Hi kualjo!
    A simple example.
    Attached Files Attached Files

  3. #3
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    Thanks 大灰狼1976, that's what I need. When I added this code to my workbook, though, I got an error saying "Ambiguous name detected: Worksheet_Change". How do I get around that?

  4. #4
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Procedures with the same names cannot exist in one module. If you already have the "own" Worksheet_Change procedure, you must merge the code into one procedure.

    Artik

  5. #5
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    Thanks Artik. After I posted that, I did some digging around and saw that I had that problem. I already had a "Private Sub Worksheet_Change(ByVal Target As Range)" for a separate function. I looked around for solutions, but they all seemed to be based on the two procedures being similar in function and purpose. My two have unrelated functions. Can they still be put together in the same procedure? If so, how I would I do that?

  6. #6
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Of course they can be combined. You just have to write the code so that the part, which concerns e.g. column A, should not be performed when the change concerns column B (and vice versa).
    If you encounter a problem combining both codes, publish them here. Someone will probably help you combine it.

    Artik

  7. #7
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    The two procedures I have are rather lengthy, so they would just overwhelm the conversation. If I just consider the first to be Procedure A and the second to be Procedure B, what kind of code would I need to have before, within, and after the two main blocks? Would it be as simple as (this is not actual code syntax, just keeping it simple):

    if active cell = "A1" then
    Procedure A
    exit sub
    Else
    Procedure B
    end sub

    If that's all I have to do, I think I can make that work.

  8. #8
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Quote Originally Posted by kualjo View Post
    if active cell = "A1" then
    Procedure A
    exit sub
    Else
    Procedure B
    end sub
    You can't use the simple "Else" because in the example you gave, "Procedure B" would be performed in any case except cell A1.
    A complex condition should be written like this:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
      If ActiveCell.Address(0, 0) = "A1" Then
        Procedure A
      ElseIf ActiveCell.Address(0, 0) = "B1" Then
        Procedure B
      End If
    
    
    End Sub
    If cell A1 is active, Procedure A will be performed. If B1 is active - Procedure B will be performed. In any other case, nothing will happen.


    Artik

  9. #9
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    Artik, I thought that last response of yours was going to be the education I needed. Unfortunately, I think the two procedures I had (both of which I had asked for and received in this forum) were written in a way that won't allow them to align with the other. Both work perfectly in isolation, but when they're tied together as in the code below. As I stated previously, these are completely separate functions and do not operate in tandem. Is there some part of either of these that can be edited in order to make both work when their respective cells change (both have dropdown list validation)?

    Sub MultiOrigin doesn't give me any errors, but it doesn't work either. Sub UnwindChart gives me a Runtime error 424, saying Object Required. Hoping you can help me figure this one out and teach me something too.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveCell.Address(0, 0) = "AB60" Then
        MultiOrigin
        ElseIf ActiveCell.Address(0, 0) = "F26" Then
            UnwindChart
            Else
            Exit Sub
    End If
    End Sub
    ----------------------------------------------------------------------------------------
    Sub MultiOrigin()
    ' To allow multiple selections in a Drop Down List in Excel (without repetition)
    Dim Oldvalue As String
    Dim Newvalue As String
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Address = "$AB$60" Then
      If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
        GoTo Exitsub
      Else: If Target.Value = "" Then GoTo Exitsub Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
          If Oldvalue = "" Then
            Target.Value = Newvalue
          Else
            If InStr(1, Oldvalue, Newvalue) = 0 Then
                Target.Value = Oldvalue & "," & Newvalue
          Else:
            Target.Value = Oldvalue
          End If
        End If
      End If
    End If
    Application.EnableEvents = True
    Exitsub:
    Application.EnableEvents = True
    End Sub
    ------------------------------------------------------------------------------------------------
    Sub UnwindChart()
    ' To highlight the ActiveX button as red when a specific item is selected in dropdown
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address <> [F26].Address Then Exit Sub
        With CommandButton1
            If Target = "Roll" Or Target = "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 Sub
    



    Thanks!
    Last edited by kualjo; 03-22-2020 at 02:36 PM. Reason: improve code clarity

  10. #10
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    I left specially commented on the lines so that you can understand what has changed in the code.
    Private Sub Worksheet_Change(ByVal Target As Range)
        If ActiveCell.Address(0, 0) = "AB60" Then
            Call MultiOrigin(ActiveCell)
        ElseIf ActiveCell.Address(0, 0) = "F26" Then
            Call UnwindChart(ActiveCell)
            'Else
            'Exit Sub
        End If
    End Sub
    '----------------------------------------------------------------------------------------
    Private Sub MultiOrigin(rngTarget As Range)
        ' To allow multiple selections in a Drop Down List in Excel (without repetition)
        Dim Oldvalue    As String
        Dim Newvalue    As String
    
        Application.EnableEvents = False    'True
    
        On Error GoTo Exitsub
    
        'If rngTarget.Address = "$AB$60" Then
        If rngTarget.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            GoTo Exitsub
        Else
            If rngTarget.Value = "" Then
                GoTo Exitsub
            Else
                'Application.EnableEvents = False
                Newvalue = rngTarget.Value
                Application.Undo
                Oldvalue = rngTarget.Value
    
                If Oldvalue = "" Then
                    rngTarget.Value = Newvalue
                Else
                    If InStr(1, Oldvalue, Newvalue) = 0 Then
                        rngTarget.Value = Oldvalue & "," & Newvalue
                    Else
                        rngTarget.Value = Oldvalue
                    End If
                End If
            End If
        End If
        'End If
        'Application.EnableEvents = True
    Exitsub:
        Application.EnableEvents = True
    End Sub
    '------------------------------------------------------------------------------------------------
    Private Sub UnwindChart(rngTarget As Range)
        ' To highlight the ActiveX button as red when a specific item is selected in dropdown
        'If rngTarget.CountLarge > 1 Then Exit Sub
        'If rngTarget.Address <> [F26].Address Then Exit Sub
        With CommandButton1
            If rngTarget.Value = "Roll" Or rngTarget.Value = "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 Sub
    Homework. Tell me why I turned off the address checking conditions in both procedures?

    Artik

  11. #11
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    Homework! I love a challenge! I think the address checking is unnecessary since the Worksheet_Change sub is already identifying the target cells for both procedures. Correct?

    This didn't seem to work right at first, but I played with a few things (like getting out of design mode ) and all seems to work as planned. I think we've got this figured out. Thanks so much for your help! Dziękuję Ci!

  12. #12
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Quote Originally Posted by kualjo View Post
    I think the address checking is unnecessary since the Worksheet_Change sub is already identifying the target cells for both procedures. Correct?

    It can still be said that as a programmer, you have consciously transferred the scope to both procedures, so you expect that the procedures will "respond" to your action within the defined scope.
    I hope you understand what Google Translator meant.

    Artik

  13. #13
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    It was intended to mean "Thank you!" You've been a big help and have helped make my project successful!

  14. #14
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    One final question. For Sub UnwindChart, there is more to the If statement than just the target cell being F26. Cell G7 also needs to be anything except blank or "Unprinted". Would it be better to add it to the Worksheet_Change sub...

    ElseIf ActiveCell.Address(0, 0) = "F26" Then
    ...or to the UnwindChart sub?

    If rngTarget.Value = "Roll" Or rngTarget.Value = "Roll_Stock" Then

    I tried adding the additional criteria to the existing code, but nothing worked. Got a little more magic?

    Thanks.

  15. #15
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Have you tried it?
    ElseIf ActiveCell.Address(0, 0) = "F26" Or ActiveCell.Address(0, 0) = "G7" Then
    BTW
    My sentence about the translator referred to the previous sentence, because I doubt that it has been translated correctly. "Dziękuję Ci" was translated correctly, although we would say "Dziękuję".

    Artik

  16. #16
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    Assuming I edit the Worksheet_Change sub, the condition to be met is more like

    ActiveCell.Address(0, 0) = "F26" AND (G7 <> "" AND G7 <> "Unprinted")

    The active cell has to be F26, but it needs to also make sure G7 does not contain these two.

    What if I just put something at the beginning of the UnwindChart sub that says IF G7 = "" OR G7 = "Unprinted" then exit sub? Just because the active cell is selected doesn't mean I want it to run through if the other criteria are not met.

    Thanks for the clarification on that translation. I sent the Polish back to English and it said 'thank you', so I figured it was OK. I need to do it again and hear it spoken. I have no idea how to say that!

  17. #17
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Now I understand what's going on.
    I am in favor of breaking complex conditions into simple ones, because it gives more control over the execution of the code. If I write the code this way:
    (...)
        ElseIf ActiveCell.Address(0, 0) = "F26" Then
            With Me.Range("G7")
                If Len(.Value) > 0 And LCase(.Value) <> LCase("Unprinted") Then
                    Call UnwindChart(ActiveCell)
                End If
            End With
        End If
    then if cell G7 is empty both conditions are checked.
    And when I write:
    (...)
        ElseIf ActiveCell.Address(0, 0) = "F26" Then
            With Me.Range("G7")
                If Len(.Value) > 0 Then
                    If LCase(.Value) <> LCase("Unprinted") Then
                        Call UnwindChart(ActiveCell)
                    End If
                End If
            End With
        End If
    only the first condition will be checked with an empty G7 cell.

    Artik

  18. #18
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Why don't you guys use Target, instead of 'activecell' ?

    ElseIf Target.address = "$F$26" Then
       If range("G7")<>"" and LCase(range("G7")) <> "unprinted" Then UnwindChart target
    End If

  19. #19
    VBAX Regular
    Joined
    Aug 2006
    Posts
    82
    Location
    Thanks to both of you for the suggestions. Both actually work, but there remains one little bump. If G7 is not initially blank or "Unprinted", then the button will light up red when either of the roll options is selected. But if G7 is then changed to "Unprinted', or if the entry is deleted, the button remains red. How can we modify this such that a change in either cell creates a combined condition that will trigger the macro and determine if the button should be red or not? Most of the time, when the G7 selection is made, it won't change. I just want to make sure that when it does, I'm not making the button a required step in the process when it's no longer needed.

    Getting closer....

  20. #20
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Private Sub Worksheet_Change(ByVal Target As Range)
      With CommandButton1
        .Font.Bold = InStr(1, "Roll_Stock", Target, 1) > 0
        .BackColor = IIf(.Font.Bold, vbRed, &HEEEEEE)
        .ForeColor = IIf(.Font.Bold, vbWhite, vbBlack)
      End With
    End Sub

Posting Permissions

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