Problem with my code! Please help me fix!

[kellem80]

Hi,
Not sure why this is happening. I have buttons located on column N and Column X. They show and hide Columns O-Q and Y-AG, respectively. I thine have code in the sheet to expand columns when activated so that the user can clearly read the choices in the data validation list. However, now that I added that second code to columns N-AG, it unhides the respective sections again. Can this be fixed? Also if there is a short cut for my expanding column code for these columns, that would also be appreciated! Thanks!

Private Sub CMD_1Click()
[INDENT]With columns("O:W")
[INDENT]If .Hidden Then
.Hidden = False
Else
.Hidden = True
End if
[/INDENT]End With
End Sub
[/INDENT]Private Sub CMD_2Click()
[INDENT]With columns("Y:AG")
[INDENT]If .Hidden Then
.Hidden = False
Else
.Hidden = True
End if
[/INDENT]End With
End Sub
[/INDENT]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 5 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (5).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 6 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (6).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 7 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (7).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 12 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (12).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 13 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (13).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 14 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (14).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 15 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (15).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 16 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (16).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 17 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (17).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 18 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (18).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 19 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (19).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 20 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (20).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 21 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (21).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 22 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (22).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 23 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (23).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 24 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (24).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 25 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (25).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 26 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (26).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 27 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (27).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 28 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (28).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 29 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (29).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 30 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (30).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 31 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (31).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 32 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (32).ColumnWidth = 17
[/INDENT]End If
[/INDENT]If Target Count >1 then Exit Sub
[INDENT]If Target.Column = 33 Then
[INDENT]Target.Columns.ColumnWidth = 40
[/INDENT]Else
[INDENT]Columns (33).ColumnWidth = 17
[/INDENT]End If
[/INDENT]End Sub

Thanks for any help in advance!!

 

[kellem80]

Hi,
However, now that I added that second code to columns N-AG, it unhides the respective sections again.

I should clarify this: When I click in Column N, it expands column N as required, but simultaneously unhides columns O-W. Same goes for Column X. I do not want the columns unhidden unless the button is clicked. cannot figure why this is happening.
thanks!

 

[kellem80]

I should clarify this: When I click in Column N, it expands column N as required, but simultaneously unhides columns O-W. Same goes for Column X. I do not want the columns unhidden unless the button is clicked. cannot figure why this is happening.
thanks!

Scratch that, When I click in ANY Cell, then columns O-AG expand?!?! (sorry they were off the screen, I didn't even realize it was happening)

 

[CharlesChuckieCharles]

This is a bit more compact for the second function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Dim BigRange As Range

Set BigRange = Application.Union(Range("E:F"), Range("L:AG"))
 
If Not (Intersect(Target, BigRange) Is Nothing) Then
   BigRange.Columns.ColumnWidth = 8.43
 
   Target.Columns.ColumnWidth = 40
End If

End Sub

 

[CharlesChuckieCharles]

Hidden is really a width

so are you trying to toggle every columns hidden status , as in what if soem are manually unhidden before the button is pressed

With Columns("O:W")
    .Hidden = Not (.Hidden)
End With

or set them to a new width

The other problem columns.. there must be more code?

 

[kellem80]

Hidden is really a width

so are you trying to toggle every columns hidden status , as in what if soem are manually unhidden before the button is pressed

With Columns("O:W")
    .Hidden = Not (.Hidden)
End With

or set them to a new width

The other problem columns.. there must be more code?

Sorry, I missed your second post:
The columns controlled by the button are either all shown or all hidden (there should be no inbetween state). The user would unhide, make selections then rehide using the button (or leave visible-his/her choice). Theory: When unhidden, the active cell should expand, then collapse back to standard width when not active. When hidden by button, should stay hidden.

This is all the code.

If I remove the code to expand the active column just for the columns that are hidden or shown by the button, then there is no problem. the other columns expand and contract without unhiding the hidden columns. The button unhides the columns for selctions, however the data validation options in these columns are barely readable.

 

[kellem80]

This is a bit more compact for the second function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Dim BigRange As Range
 
Set BigRange = Application.Union(Range("E:F"), Range("L:AG"))
 
If Not (Intersect(Target, BigRange) Is Nothing) Then
   BigRange.Columns.ColumnWidth = 8.43
 
   Target.Columns.ColumnWidth = 40
End If
 
End Sub

thanks, this worked except I had to remove the range for L:AG because it began doing the same error where if I clicked on "E" for example, it unhid M-U.
Is there another way to expand a validation list drop down?? OR something that will prevent this from unhiding those other columns?