Changing ActiveX control properties based on cell text

[kualjo]

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!

[大灰狼1976]

Hi kualjo!
A simple example.

[kualjo]

Thanks 大灰狼1976 (http://www.vbaexpress.com/forum/member.php?70849-大灰狼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?

[Artik]

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

[kualjo]

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?

[Artik]

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

[kualjo]

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.

[Artik]

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

[kualjo]

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!

[Artik]

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

[kualjo]

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!

[Artik]

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

[kualjo]

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

[kualjo]

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.

[Artik]

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

[kualjo]

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!

[Artik]

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 Ifonly the first condition will be checked with an empty G7 cell.

Artik

[snb]

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

[kualjo]

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

[snb]

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

[kualjo]

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.

[snb]

I changed my dropdown selections

What dropdown selections, where ?

Post a sample file.

[kualjo]

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?

[kualjo]

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!

[kualjo]

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.

[p45cal]

http://www.vbaexpress.com/forum/faq.php?faq=vb3_reading_posting#faq_vb3_attachments

[kualjo]

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.

[p45cal]

26259

[kualjo]

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

[p45cal]

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.

[kualjo]

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!