Hiding Columns VBA & Validation Dropdown Lists

PEC-Memphis

New Member
Joined
Apr 15, 2009
Messages
19
I posted a question a week or so ago, where a VBA would run on one worksheet but not another. It seems that I didn't understand the problem. After goobing around some more, the problem is related to using the validation pull-down. Here is the VBA:
Private Sub Worksheet_Change(ByVal Target As Range)

If Range("D5") <> "DBS" Then
Columns("J").EntireColumn.Hidden = True
Else
Columns("J").EntireColumn.Hidden = False
End If

End Sub

Seems simple enough?

If I manually enter "DBS" in cell D5, col "J" is unhidden, if I manually enter anything else in D5 col "J" is hidden; ie. the VBS works as intended.

Here's the problem: If I enter "DBS" using the validation dropdown it doesn't work, ie col "J" remains in the current state of "hidden-ness", unless I put the cursor in the formula bar after "DBS" and click on the check mark or hit enter.

I need for the users to be able to use the drop down list because the value in D5 also is used with VLOOKUP function to enter data in other cells from a library of data.
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

scott_n_phnx

Active Member
Joined
Sep 28, 2006
Messages
445
Try this.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D5")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If Target.Value <> "DBS" Then
Columns("J").EntireColumn.Hidden = True
Else
Columns("J").EntireColumn.Hidden = False
End If
End Sub
 

PEC-Memphis

New Member
Joined
Apr 15, 2009
Messages
19
Try this.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D5")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If Target.Value <> "DBS" Then
Columns("J").EntireColumn.Hidden = True
Else
Columns("J").EntireColumn.Hidden = False
End If
End Sub

Thanks for the reply, but your VBA "acts" the same as mine.

It only changes the col "hide/unhide" state if the cell entry is manual or cursor placed in formula bar then "enter".
 

PEC-Memphis

New Member
Joined
Apr 15, 2009
Messages
19
This is very peculiar.

I created a new workbook. Used the same VBA, with the same validation list, and the VBA works as is expected; ie. whenever "DBS" is entered by the pulldown, col "J" is unhidden - any other data col "J" is hidden.

Also, my "undo" function seems to have been disabled. I've shut down and restarted and the problems still exist.
 

PEC-Memphis

New Member
Joined
Apr 15, 2009
Messages
19

ADVERTISEMENT

This is very peculiar.

I created a new workbook. Used the same VBA, with the same validation list, and the VBA works as is expected; ie. whenever "DBS" is entered by the pulldown, col "J" is unhidden - any other data col "J" is hidden.

Also, my "undo" function seems to have been disabled. I've shut down and restarted and the problems still exist.

If I disable macros when starting, the "undo" function appears to work correctly, but of course none of my VBAs or UDF's work.
 

scott_n_phnx

Active Member
Joined
Sep 28, 2006
Messages
445
When you say that your Undo function seems to be disabled, how do you mean? Does it just not appear after you have the macro running and then column J gets hidden, there is no undo option? The undo function doesn't work after running macros. The macro will clear any "saved" steps so that you can no longer undo. If you mean that the macro hides J and then you do other things and there is no undo, then you would need to check to see if there are any other macros running that might be preventing the undo.
 

PEC-Memphis

New Member
Joined
Apr 15, 2009
Messages
19

ADVERTISEMENT

As soon as the workbook is opened with macros enabled, the undo function in "grayed", it only "un-grays" while typing in a cell. As soon as the cell contents are entered (check or enter), the undo function is "grayed" - both in the icon and in the pull down menu.
 

scott_n_phnx

Active Member
Joined
Sep 28, 2006
Messages
445
Are there any other macros running, besides the one we are working on? If there are no other macros, check out this page to see if you can fix it.

How to troubleshoot startup problems in Excel


Also, is this the only workbook that the issue occurs on, or does it happen to others as well?
 
Last edited:

PEC-Memphis

New Member
Joined
Apr 15, 2009
Messages
19
The "undo" problem is only on the worksheet that has the script for the hide/unhide col "J". If I remove the script:
.
If Range("D5") <> "DBS" Then
Columns("J").EntireColumn.Hidden = True
Else
Columns("J").EntireColumn.Hidden = False
End If

End Sub​
.

The "undo" problem goes away.
.
I went back and used your script:
.
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D5")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If Target.Value <> "DBS" Then
Columns("J").EntireColumn.Hidden = True
Else
Columns("J").EntireColumn.Hidden = False
End If
End Sub​
.

It doensn't seem to have the "undo" problem. I don't know why but it doesn't. In 2002, both scripts require manual entry to properly hide/unhide col "J" for this particular workbook - but they seem to work in 2010.

I created another workbook (VBS test) and used your script, and I added the UDF's from the "non-working" workbook. Everything works in the new workbook.

The "non-working"** wookbook is getting fairly large - 606Kb and will likely double this size before it is finished. Very little of this is entered data, but rather calculated data using (mostly) UDFs. Obviously I have quite a bit of time in the workbook, and starting over is an unattracive option (especially of the same problem manifests itself again).



**Non-working = the data in D5 has to be manually entered (or cursor placed in formula bar + 'enter' or "check') for col "J" to be hidden/unhidden
 

scott_n_phnx

Active Member
Joined
Sep 28, 2006
Messages
445
Unfortunately, I do not know what would cause this issue. I know this is a silly question, but I will ask anyway. You are only using one or the other of the two codes, correct? The only reason that I can think of why the new one does not cause the issue is the use of the Intersect(Target) entry. It should limit/focus where the code is looking.
 

Forum statistics

Threads
1,137,300
Messages
5,680,699
Members
419,928
Latest member
dolincasting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top