Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single Selection ListBoxes
Can anyone kindly help with the following:
How do I retrieve a value from a single selection listbox and then use the value returned to run an autofilter. FYI the contents of my listbox represent both the worksheet name as well as the range name. I'm used to coding multi- select listboxes so this should be even easier but, like yesterday and my naming ranges question, I'm still having a mental block. My autofilter code is below it's not correct but hopefully you'll get the idea With Sheets.LstSheet.Value.Range = LstSheet.Value .AutoFilter Field:=2, Criteria1:="M" .AutoFilter Field:=3, Criteria1:=vChanArr(n) End With Many thanks Jacqui |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single Selection ListBoxes
Jacqui,
Is there something missing? Where is Filed=1, what is vCharArr(n), and what does this mean - With Sheets.LstSheet.Value.Range = LstSheet.Value -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "jacqui" wrote in message ... Can anyone kindly help with the following: How do I retrieve a value from a single selection listbox and then use the value returned to run an autofilter. FYI the contents of my listbox represent both the worksheet name as well as the range name. I'm used to coding multi- select listboxes so this should be even easier but, like yesterday and my naming ranges question, I'm still having a mental block. My autofilter code is below it's not correct but hopefully you'll get the idea With Sheets.LstSheet.Value.Range = LstSheet.Value .AutoFilter Field:=2, Criteria1:="M" .AutoFilter Field:=3, Criteria1:=vChanArr(n) End With Many thanks Jacqui |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single Selection ListBoxes
assume the name of your listbox is ListBox1
With Activesheet .AutoFilter Field:=2, Criteria1:=.Listbox1.Value .AutoFilter Field:=3, Criteria1:=vChanArr(n) End With -- Regards, Tom Ogilvy "jacqui" wrote in message ... Can anyone kindly help with the following: How do I retrieve a value from a single selection listbox and then use the value returned to run an autofilter. FYI the contents of my listbox represent both the worksheet name as well as the range name. I'm used to coding multi- select listboxes so this should be even easier but, like yesterday and my naming ranges question, I'm still having a mental block. My autofilter code is below it's not correct but hopefully you'll get the idea With Sheets.LstSheet.Value.Range = LstSheet.Value .AutoFilter Field:=2, Criteria1:="M" .AutoFilter Field:=3, Criteria1:=vChanArr(n) End With Many thanks Jacqui |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single Selection ListBoxes
Bob
yeah you're right my missing bit is a variable. However, how do I make a variable in one module available in another? In my forms module I've said Public sRange as string Then under Private Sub LstSheet_Click() I've said sRange = LstSheet.Value This works fine except that the next line of my code is Run_Channel_Reports This calls a sub in my general module where I've used the following With Sheets(sRange).Range(sRange) .AutoFilter Field:=2, Criteria1:="M" .AutoFilter Field:=3, Criteria1:=vChanArr(n) End With When I step through the code VBA says Variable not defined because it doesn't recognise sRange in the active module. Can you kindly help? PS In answer to your questions Field 1 is not needed for my autofilter. I'm just filtering on fields 2 and 3 only of my datafile. This is deliberate, trust me. vChanArr(n) is the sort criteria selected by the user from a main menu, the value held in vChanArr will correspond with data in field 3 of my datafile. and as for... what does this mean With Sheets.LstSheet.Value.Range = LstSheet.Value Well completely forget it, it's a classic example of my very poor programming!!! Many thanks Jacqui -----Original Message----- Jacqui, Is there something missing? Where is Filed=1, what is vCharArr(n), and what does this mean - With Sheets.LstSheet.Value.Range = LstSheet.Value -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "jacqui" wrote in message ... Can anyone kindly help with the following: How do I retrieve a value from a single selection listbox and then use the value returned to run an autofilter. FYI the contents of my listbox represent both the worksheet name as well as the range name. I'm used to coding multi- select listboxes so this should be even easier but, like yesterday and my naming ranges question, I'm still having a mental block. My autofilter code is below it's not correct but hopefully you'll get the idea With Sheets.LstSheet.Value.Range = LstSheet.Value .AutoFilter Field:=2, Criteria1:="M" .AutoFilter Field:=3, Criteria1:=vChanArr(n) End With Many thanks Jacqui . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single Selection ListBoxes
Declare
Public sRange as string in a general module, not in the sheet module or userform module or thisworkbook module Then it will be visible to all your modules -- Regards, Tom Ogilvy "jacqui" wrote in message ... Bob yeah you're right my missing bit is a variable. However, how do I make a variable in one module available in another? In my forms module I've said Public sRange as string Then under Private Sub LstSheet_Click() I've said sRange = LstSheet.Value This works fine except that the next line of my code is Run_Channel_Reports This calls a sub in my general module where I've used the following With Sheets(sRange).Range(sRange) .AutoFilter Field:=2, Criteria1:="M" .AutoFilter Field:=3, Criteria1:=vChanArr(n) End With When I step through the code VBA says Variable not defined because it doesn't recognise sRange in the active module. Can you kindly help? PS In answer to your questions Field 1 is not needed for my autofilter. I'm just filtering on fields 2 and 3 only of my datafile. This is deliberate, trust me. vChanArr(n) is the sort criteria selected by the user from a main menu, the value held in vChanArr will correspond with data in field 3 of my datafile. and as for... what does this mean With Sheets.LstSheet.Value.Range = LstSheet.Value Well completely forget it, it's a classic example of my very poor programming!!! Many thanks Jacqui -----Original Message----- Jacqui, Is there something missing? Where is Filed=1, what is vCharArr(n), and what does this mean - With Sheets.LstSheet.Value.Range = LstSheet.Value -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "jacqui" wrote in message ... Can anyone kindly help with the following: How do I retrieve a value from a single selection listbox and then use the value returned to run an autofilter. FYI the contents of my listbox represent both the worksheet name as well as the range name. I'm used to coding multi- select listboxes so this should be even easier but, like yesterday and my naming ranges question, I'm still having a mental block. My autofilter code is below it's not correct but hopefully you'll get the idea With Sheets.LstSheet.Value.Range = LstSheet.Value .AutoFilter Field:=2, Criteria1:="M" .AutoFilter Field:=3, Criteria1:=vChanArr(n) End With Many thanks Jacqui . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Synchronize data in two LISTBOXes | Excel Discussion (Misc queries) | |||
start and end dates within listboxes | Excel Worksheet Functions | |||
How do I refresh the selection listboxes on a pivot table? | Excel Discussion (Misc queries) | |||
Linked ListBoxes | Excel Programming | |||
Listboxes get unchecked on save-as | Excel Programming |