Hiding Columns VBA & Validation Dropdown Lists

[PEC-Memphis]

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.

 

[scott_n_phnx]

Try this.

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]

Try this.

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]

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]

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]

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]

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]

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?

 

[PEC-Memphis]

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]

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.