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!

Hi kualjo!
A simple example.

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?

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

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?

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

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.

Quote:

---

Originally Posted by kualjo

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:

Code:

---

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

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.

Code:

---

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!

I left specially commented on the lines so that you can understand what has changed in the code.

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

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 :doh:) and all seems to work as planned. I think we've got this figured out. Thanks so much for your help! Dziękuję Ci!

Quote:

---

Originally Posted by kualjo

I think the address checking is unnecessary since the Worksheet_Change sub is already identifying the target cells for both procedures. Correct?

---

:thumb
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

It was intended to mean "Thank you!" You've been a big help and have helped make my project successful!

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...

Code:

---

ElseIf ActiveCell.Address(0, 0) = "F26" Then

---

...or to the UnwindChart sub?

Code:

---

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.

Have you tried it?

Code:

---

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

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!

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:

Code:

---

(...)
    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:

Code:

---

(...)
    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

Why don't you guys use Target, instead of 'activecell' ?

Code:

---

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

---

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....

Code:

---

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

---

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.

Quote:

---

I changed my dropdown selections

---

What dropdown selections, where ?

Post a sample file.

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?

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!

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.

http://www.vbaexpress.com/forum/faq....b3_attachments

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.

Attachment 26259

They had it cleverly hidden under Go Advanced. Now I know.

You don't need the UnwindChartPrint and UnwindChartItem macros, this by itself should do it:

Code:

---

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.

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!