Single Selection ListBoxes

[jacqui]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

assume the name of your listbox is ListBox1

With Activesheet
.AutoFilter Field:=2, Criteria1:=.Listbox1.Value
.AutoFilter Field:=3, Criteria1:=vChanArr(n)
End With

 

[jacqui]

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

 

[Tom Ogilvy]

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