VBA code doesn't run when value selected from list

I

ivory_kitten

i have a VBA code to show/hide rows depending on the value of a cell, the
values can either be selected from the list or typed in, my problem is when
the user selects the value from the list nothing happens, the code only
executes when typing and pressing enter.

Here's the code:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
'Conditions & Ranges'
Dim rng As Range, rng2 As Range, rng3 As Range
Dim wf As WorksheetFunction
Dim cond1 As Boolean, cond2 As Boolean, cond3 As Boolean, cond4 As Boolean

Set wf = Application.WorksheetFunction
Set rng = Me.Range("OU1") 'Option 1'
Set rng2 = Me.Range("OU2") 'Option 2'
Set rng3 = Me.Range("OU3") 'Option 3'
cond1 = (UCase(rng3.Value) = "YES")
cond2 = (UCase(rng3.Value) = "NO")
cond3 = (UCase(rng2.Value) = "YES")
cond4 = (UCase(rng2.Value) = "NO")

'Hides Packing Prices'
[38:38, 47:47].EntireRow.Hidden = wf.And(cond2, cond4)
[37:37, 46:46].EntireRow.Hidden = wf.Or(wf.And(cond1, cond3),
wf.And(cond2, cond4))

'Hides depending on Type'
[9:9].EntireRow.Hidden = (rng3.Value = "YES")
[10:21, 35:35, 44:44].EntireRow.Hidden = (rng3.Value = "NO")
'Hides Option 2'
If Not Intersect(rng, target) Is Nothing Then
[36:36, 45:45].EntireRow.Hidden = IsEmpty(rng.Value)
End If
Application.ScreenUpdating = True
End Sub
 
Z

Zone

How about this? Copy this code and paste it in the same module as your
Worksheet_Change code.
James

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim targ As Range
Set targ = Target
Worksheet_Change targ
End Sub
 
T

Tom Ogilvy

Is the dropdown built using Data validation? If so, then what version of
Excel are you using. If xl97, the problem you describe did exist. In other
versions, there should not be a problem.

If not using data validation, then what type of control are you using?
Control toolbox Toolbar Combobox?
Dropdown box from the forms toolbar?
 
I

ivory_kitten

Hi Tom, I am using Excel 2003. I have never used xl97 for this project.

And yes it is a list using Data Validation.

Tom Ogilvy said:
Is the dropdown built using Data validation? If so, then what version of
Excel are you using. If xl97, the problem you describe did exist. In other
versions, there should not be a problem.

If not using data validation, then what type of control are you using?
Control toolbox Toolbar Combobox?
Dropdown box from the forms toolbar?

--
Regards,
Tom Ogilvy


ivory_kitten said:
i have a VBA code to show/hide rows depending on the value of a cell, the
values can either be selected from the list or typed in, my problem is when
the user selects the value from the list nothing happens, the code only
executes when typing and pressing enter.

Here's the code:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
'Conditions & Ranges'
Dim rng As Range, rng2 As Range, rng3 As Range
Dim wf As WorksheetFunction
Dim cond1 As Boolean, cond2 As Boolean, cond3 As Boolean, cond4 As Boolean

Set wf = Application.WorksheetFunction
Set rng = Me.Range("OU1") 'Option 1'
Set rng2 = Me.Range("OU2") 'Option 2'
Set rng3 = Me.Range("OU3") 'Option 3'
cond1 = (UCase(rng3.Value) = "YES")
cond2 = (UCase(rng3.Value) = "NO")
cond3 = (UCase(rng2.Value) = "YES")
cond4 = (UCase(rng2.Value) = "NO")

'Hides Packing Prices'
[38:38, 47:47].EntireRow.Hidden = wf.And(cond2, cond4)
[37:37, 46:46].EntireRow.Hidden = wf.Or(wf.And(cond1, cond3),
wf.And(cond2, cond4))

'Hides depending on Type'
[9:9].EntireRow.Hidden = (rng3.Value = "YES")
[10:21, 35:35, 44:44].EntireRow.Hidden = (rng3.Value = "NO")
'Hides Option 2'
If Not Intersect(rng, target) Is Nothing Then
[36:36, 45:45].EntireRow.Hidden = IsEmpty(rng.Value)
End If
Application.ScreenUpdating = True
End Sub
Click to expand...
Click to expand...
 
I

ivory_kitten

It kind of works, but only when you click out of the cell, not when you
select a different value from the list.

And now every time you change cells the screen flashes!

Zone said:
How about this? Copy this code and paste it in the same module as your
Worksheet_Change code.
James

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim targ As Range
Set targ = Target
Worksheet_Change targ
End Sub

ivory_kitten said:
i have a VBA code to show/hide rows depending on the value of a cell, the
values can either be selected from the list or typed in, my problem is
when
the user selects the value from the list nothing happens, the code only
executes when typing and pressing enter.

Here's the code:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
'Conditions & Ranges'
Dim rng As Range, rng2 As Range, rng3 As Range
Dim wf As WorksheetFunction
Dim cond1 As Boolean, cond2 As Boolean, cond3 As Boolean, cond4 As
Boolean

Set wf = Application.WorksheetFunction
Set rng = Me.Range("OU1") 'Option 1'
Set rng2 = Me.Range("OU2") 'Option 2'
Set rng3 = Me.Range("OU3") 'Option 3'
cond1 = (UCase(rng3.Value) = "YES")
cond2 = (UCase(rng3.Value) = "NO")
cond3 = (UCase(rng2.Value) = "YES")
cond4 = (UCase(rng2.Value) = "NO")

'Hides Packing Prices'
[38:38, 47:47].EntireRow.Hidden = wf.And(cond2, cond4)
[37:37, 46:46].EntireRow.Hidden = wf.Or(wf.And(cond1, cond3),
wf.And(cond2, cond4))

'Hides depending on Type'
[9:9].EntireRow.Hidden = (rng3.Value = "YES")
[10:21, 35:35, 44:44].EntireRow.Hidden = (rng3.Value = "NO")
'Hides Option 2'
If Not Intersect(rng, target) Is Nothing Then
[36:36, 45:45].EntireRow.Hidden = IsEmpty(rng.Value)
End If
Application.ScreenUpdating = True
End Sub
Click to expand...
Click to expand...
 
D

Dave Peterson

Maybe the code is running, but isn't doing what you want.

You use this:
(rng3.Value = "YES")
maybe you meant:
(ucase(rng3.Value) = "YES")



ivory_kitten said:
Hi Tom, I am using Excel 2003. I have never used xl97 for this project.

And yes it is a list using Data Validation.

Tom Ogilvy said:
Is the dropdown built using Data validation? If so, then what version of
Excel are you using. If xl97, the problem you describe did exist. In other
versions, there should not be a problem.

If not using data validation, then what type of control are you using?
Control toolbox Toolbar Combobox?
Dropdown box from the forms toolbar?

--
Regards,
Tom Ogilvy


ivory_kitten said:
i have a VBA code to show/hide rows depending on the value of a cell, the
values can either be selected from the list or typed in, my problem is when
the user selects the value from the list nothing happens, the code only
executes when typing and pressing enter.

Here's the code:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
'Conditions & Ranges'
Dim rng As Range, rng2 As Range, rng3 As Range
Dim wf As WorksheetFunction
Dim cond1 As Boolean, cond2 As Boolean, cond3 As Boolean, cond4 As Boolean

Set wf = Application.WorksheetFunction
Set rng = Me.Range("OU1") 'Option 1'
Set rng2 = Me.Range("OU2") 'Option 2'
Set rng3 = Me.Range("OU3") 'Option 3'
cond1 = (UCase(rng3.Value) = "YES")
cond2 = (UCase(rng3.Value) = "NO")
cond3 = (UCase(rng2.Value) = "YES")
cond4 = (UCase(rng2.Value) = "NO")

'Hides Packing Prices'
[38:38, 47:47].EntireRow.Hidden = wf.And(cond2, cond4)
[37:37, 46:46].EntireRow.Hidden = wf.Or(wf.And(cond1, cond3),
wf.And(cond2, cond4))

'Hides depending on Type'
[9:9].EntireRow.Hidden = (rng3.Value = "YES")
[10:21, 35:35, 44:44].EntireRow.Hidden = (rng3.Value = "NO")
'Hides Option 2'
If Not Intersect(rng, target) Is Nothing Then
[36:36, 45:45].EntireRow.Hidden = IsEmpty(rng.Value)
End If
Application.ScreenUpdating = True
End Sub
Click to expand...
Click to expand...
Click to expand...
 
I

ivory_kitten

Hi thanks for your response,
I assume you mean this bit
'Hides depending on Type'
[9:9].EntireRow.Hidden = (rng3.Value = "YES")
[10:21, 35:35, 44:44].EntireRow.Hidden = (rng3.Value = "NO")
Click to expand...
Click to expand...
Click to expand...

I tried changing them as you suggested, but still doesn't make a difference!


Dave Peterson said:
Maybe the code is running, but isn't doing what you want.

You use this:
(rng3.Value = "YES")
maybe you meant:
(ucase(rng3.Value) = "YES")



ivory_kitten said:
Hi Tom, I am using Excel 2003. I have never used xl97 for this project.

And yes it is a list using Data Validation.

Tom Ogilvy said:
Is the dropdown built using Data validation? If so, then what version of
Excel are you using. If xl97, the problem you describe did exist. In other
versions, there should not be a problem.

If not using data validation, then what type of control are you using?
Control toolbox Toolbar Combobox?
Dropdown box from the forms toolbar?

--
Regards,
Tom Ogilvy


:

i have a VBA code to show/hide rows depending on the value of a cell, the
values can either be selected from the list or typed in, my problem is when
the user selects the value from the list nothing happens, the code only
executes when typing and pressing enter.

Here's the code:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
'Conditions & Ranges'
Dim rng As Range, rng2 As Range, rng3 As Range
Dim wf As WorksheetFunction
Dim cond1 As Boolean, cond2 As Boolean, cond3 As Boolean, cond4 As Boolean

Set wf = Application.WorksheetFunction
Set rng = Me.Range("OU1") 'Option 1'
Set rng2 = Me.Range("OU2") 'Option 2'
Set rng3 = Me.Range("OU3") 'Option 3'
cond1 = (UCase(rng3.Value) = "YES")
cond2 = (UCase(rng3.Value) = "NO")
cond3 = (UCase(rng2.Value) = "YES")
cond4 = (UCase(rng2.Value) = "NO")

'Hides Packing Prices'
[38:38, 47:47].EntireRow.Hidden = wf.And(cond2, cond4)
[37:37, 46:46].EntireRow.Hidden = wf.Or(wf.And(cond1, cond3),
wf.And(cond2, cond4))

'Hides depending on Type'
[9:9].EntireRow.Hidden = (rng3.Value = "YES")
[10:21, 35:35, 44:44].EntireRow.Hidden = (rng3.Value = "NO")
'Hides Option 2'
If Not Intersect(rng, target) Is Nothing Then
[36:36, 45:45].EntireRow.Hidden = IsEmpty(rng.Value)
End If
Application.ScreenUpdating = True
End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Maybe it's time to add some msgbox's to your code to make sure it's firing when
you make a change.

ivory_kitten said:
Hi thanks for your response,
I assume you mean this bit
'Hides depending on Type'
[9:9].EntireRow.Hidden = (rng3.Value = "YES")
[10:21, 35:35, 44:44].EntireRow.Hidden = (rng3.Value = "NO")
Click to expand...
Click to expand...

I tried changing them as you suggested, but still doesn't make a difference!

Dave Peterson said:
Maybe the code is running, but isn't doing what you want.

You use this:
(rng3.Value = "YES")
maybe you meant:
(ucase(rng3.Value) = "YES")



ivory_kitten said:
Hi Tom, I am using Excel 2003. I have never used xl97 for this project.

And yes it is a list using Data Validation.

:

Is the dropdown built using Data validation? If so, then what version of
Excel are you using. If xl97, the problem you describe did exist. In other
versions, there should not be a problem.

If not using data validation, then what type of control are you using?
Control toolbox Toolbar Combobox?
Dropdown box from the forms toolbar?

--
Regards,
Tom Ogilvy


:

i have a VBA code to show/hide rows depending on the value of a cell, the
values can either be selected from the list or typed in, my problem is when
the user selects the value from the list nothing happens, the code only
executes when typing and pressing enter.

Here's the code:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
'Conditions & Ranges'
Dim rng As Range, rng2 As Range, rng3 As Range
Dim wf As WorksheetFunction
Dim cond1 As Boolean, cond2 As Boolean, cond3 As Boolean, cond4 As Boolean

Set wf = Application.WorksheetFunction
Set rng = Me.Range("OU1") 'Option 1'
Set rng2 = Me.Range("OU2") 'Option 2'
Set rng3 = Me.Range("OU3") 'Option 3'
cond1 = (UCase(rng3.Value) = "YES")
cond2 = (UCase(rng3.Value) = "NO")
cond3 = (UCase(rng2.Value) = "YES")
cond4 = (UCase(rng2.Value) = "NO")

'Hides Packing Prices'
[38:38, 47:47].EntireRow.Hidden = wf.And(cond2, cond4)
[37:37, 46:46].EntireRow.Hidden = wf.Or(wf.And(cond1, cond3),
wf.And(cond2, cond4))

'Hides depending on Type'
[9:9].EntireRow.Hidden = (rng3.Value = "YES")
[10:21, 35:35, 44:44].EntireRow.Hidden = (rng3.Value = "NO")
'Hides Option 2'
If Not Intersect(rng, target) Is Nothing Then
[36:36, 45:45].EntireRow.Hidden = IsEmpty(rng.Value)
End If
Application.ScreenUpdating = True
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conditional row hide 2
simple code hide/show rows with cell = empty, set value or any val 4
Fill Color Macro 5
Excel 2007 VBA shape fill colour problem 0
Assistance with VBA code on MAC 2
Count Specific Total Numbers from a Set 22
rngFound not finding correct value when more than one match 0
macros code & color based functions help 2

Top