Entering value through dropdown list does not induce script change

[Ali Atwi]

Office 2007

I have made a crude meeting room booking system.

One of the additions I added was a cell to choose what week to view. Basically if you type week 1 in cell A2, the rest of the columns that contain the other weeks will be hidden.

This is my code. I am a scripting newbie, but I modified this to suit my sheet.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range
Set Changed = Range("A2")
If Not Intersect(Target, Changed) Is Nothing Then
Range("B:CL").EntireColumn.Hidden = False
Select Case UCase(Target.Value)
Case "WEEK 1"
Range("J:CK").EntireColumn.Hidden = True
Range("B:I").EntireColumn.Hidden = False
Case "WEEK 2"
Range("B:I", "AD:CK").EntireColumn.Hidden = True
Range("J:AC").EntireColumn.Hidden = False
Case "WEEK 3"
Range("B:AC", "AX:CK").EntireColumn.Hidden = True
Range("AD:AW").EntireColumn.Hidden = False
Case "WEEK 4"
Range("B:AW", "BP:CK").EntireColumn.Hidden = True
Range("AX:BQ").EntireColumn.Hidden = False
Case "WEEK 5"
Range("B:BQ").EntireColumn.Hidden = True
Range("BR:CK").EntireColumn.Hidden = False
Case "Entire Month"
Range("B:CL").EntireColumn.Hidden = False
End Select

The code works perfectly.

However Once I linked a dropdown list (active x one) to the cell A2, and I choose the values from the Dropdown, the sheet will not hide/unhide the columns unless I double click on A2 as if to edit it and press enter.

Is there anyway to induce the changes through the dropdown so that I do not have to directly touch A2?

 

[JoeMo]

If your drop down is an active-x control, changing a selection in the control itself is not a worksheet_change event, even though you may have the control linked to a worksheet cell that does change when you change the control selection. If you right-click the control while in Design Mode and choose View Code you can then add code that will be executed whenever the control is clicked (and you have exited Design Mode).

Alternatively, changing the selection in a validation drop down list does constitute a worksheet_change event and can be used directly with your existing code.

 

[Ali Atwi]

The validation drop down list has fixed font size and can hardly be seen. So i need that drop down to be combobox active x.

I tried entering this code, but it gives a compile error. How do I make the code related to the drop down click?

Private Sub ComboBox1_Change(ByVal Target As Range)
Dim Changed As Range
Set Changed = Range("A2")
If Not Intersect(Target, Changed) Is Nothing Then
Range("B:CL").EntireColumn.Hidden = False
Select Case UCase(Target.Value)
Case "WEEK 1"
Range("J:CK").EntireColumn.Hidden = True
Range("B:I").EntireColumn.Hidden = False
Case "WEEK 2"
Range("B:I", "AD:CK").EntireColumn.Hidden = True
Range("J:AC").EntireColumn.Hidden = False
Case "WEEK 3"
Range("B:AC", "AX:CK").EntireColumn.Hidden = True
Range("AD:AW").EntireColumn.Hidden = False
Case "WEEK 4"
Range("B:AW", "BP:CK").EntireColumn.Hidden = True
Range("AX:BQ").EntireColumn.Hidden = False
Case "WEEK 5"
Range("B:BQ").EntireColumn.Hidden = True
Range("BR:CK").EntireColumn.Hidden = False
Case "Entire Month"
Range("B:CL").EntireColumn.Hidden = False
End Select
End Sub

 

[JoeMo]

What line does the compile error point to? What cell is the combo box linked to? There will not be an intersect if the combo box is changed.

 

[Ali Atwi]

the first line, and the combo box is linked to A2

ByVal Target As Range <=== this one
and in later lines it points to the word "Target" saying it is not defined.

 

[JoeMo]

the first line, and the combo box is linked to A2

ByVal Target As Range <=== this one
and in later lines it points to the word "Target" saying it is not defined.

The combobox_change event does not use any arguments. It is triggered whenever the combo box setting is changed. Can you describe what you want to happen when a change is made to the setting?

 

[Ali Atwi]

After a bit of research I tried this code, and thank you for telling me that I do no have to place arguments, I just don't know any of those details. So i deleted the arguments and tried to use the basic logic of the code and it works like a charm

Mucha gracias.

Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "WEEK 1"
Range("J:CK").EntireColumn.Hidden = True
Range("B:I").EntireColumn.Hidden = False
Case "WEEK 2"
Range("B:I", "AD:CK").EntireColumn.Hidden = True
Range("J:AC").EntireColumn.Hidden = False
Case "WEEK 3"
Range("B:AC", "AX:CK").EntireColumn.Hidden = True
Range("AD:AW").EntireColumn.Hidden = False
Case "WEEK 4"
Range("B:AW", "BP:CK").EntireColumn.Hidden = True
Range("AX:BQ").EntireColumn.Hidden = False
Case "WEEK 5"
Range("B:BQ").EntireColumn.Hidden = True
Range("BR:CK").EntireColumn.Hidden = False
Case "All"
Range("B:CL").EntireColumn.Hidden = False
End Select
End Sub

 

[JoeMo]

After a bit of research I tried this code, and thank you for telling me that I do no have to place arguments, I just don't know any of those details. So i deleted the arguments and tried to use the basic logic of the code and it works like a charm

Mucha gracias.

Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "WEEK 1"
Range("J:CK").EntireColumn.Hidden = True
Range("B:I").EntireColumn.Hidden = False
Case "WEEK 2"
Range("B:I", "AD:CK").EntireColumn.Hidden = True
Range("J:AC").EntireColumn.Hidden = False
Case "WEEK 3"
Range("B:AC", "AX:CK").EntireColumn.Hidden = True
Range("AD:AW").EntireColumn.Hidden = False
Case "WEEK 4"
Range("B:AW", "BP:CK").EntireColumn.Hidden = True
Range("AX:BQ").EntireColumn.Hidden = False
Case "WEEK 5"
Range("B:BQ").EntireColumn.Hidden = True
Range("BR:CK").EntireColumn.Hidden = False
Case "All"
Range("B:CL").EntireColumn.Hidden = False
End Select
End Sub

Good work and thanks for the reply.