Problem with my code! Please help me fix!

kellem80

Board Regular
Joined
Apr 2, 2008
Messages
95
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!
Code:
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

Board Regular
Joined
Apr 2, 2008
Messages
95
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

Board Regular
Joined
Apr 2, 2008
Messages
95
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

Well-known Member
Joined
May 10, 2011
Messages
2,153
This is a bit more compact for the second function

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

Well-known Member
Joined
May 10, 2011
Messages
2,153
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

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

Board Regular
Joined
Apr 2, 2008
Messages
95
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

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

Board Regular
Joined
Apr 2, 2008
Messages
95
This is a bit more compact for the second function

Code:
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?
 

Forum statistics

Threads
1,082,630
Messages
5,366,656
Members
400,909
Latest member
ola97316

Some videos you may like

This Week's Hot Topics

Top