Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code doesn't run when value selected from list
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code doesn't run when value selected from list
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code doesn't run when value selected from list
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code doesn't run when value selected from list
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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code doesn't run when value selected from list
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" wrote: 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code doesn't run when value selected from list
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 wrote: 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" wrote: 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" wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code doesn't run when value selected from list
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") I tried changing them as you suggested, but still doesn't make a difference! "Dave Peterson" wrote: 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 wrote: 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" wrote: 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" wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code doesn't run when value selected from list
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 wrote: 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") I tried changing them as you suggested, but still doesn't make a difference! "Dave Peterson" wrote: 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 wrote: 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" wrote: 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" wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If word from list selected adjoining cell to downfill with a code | Excel Discussion (Misc queries) | |||
vb code to copy and list selected row to diff sheets | Excel Discussion (Misc queries) | |||
List and subtotal selected items, then print separate item list | Excel Worksheet Functions | |||
A validated List which link to selected cells according to what is selected on the list | Excel Worksheet Functions | |||
Populating dropdown list 2 with data depending upon what was selected in list 1 | Excel Programming |