Listbox problem

[llan_man]

Hi

I want to be able to use the item that a user selects from a list box in the following code. The listbox is called "viewsection". The value chosen is displayed in a cell on a specified worksheet and is also used as the criteria for an auto filter.

I was using this code with a combo box but as soon as I switched this to a list box it stopped working and I haven't got a clue why.

Any help will be very appreciated.

Thanks

Harv

Private Sub cmdslctadj_Click()

Me.Hide

Sheets("changes").Activate
Sheets("changes").Range("a10:t200").Select


Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:=viewsection
Sheets("changes").Range("d8") = viewsection
Sheets("changes").Range("d8").Select

End Sub

 

[erik.van.geit]

Hello,

after some tryout
it's impossible for me to generate errors, not using combobox neighter listbox
so let's find out ...
where do you get the error ?
or what do you mean by "stopped working"

SIDENOTE:
you can shorten your code this way

Me.Hide

'Sheets("changes").Activate
Sheets("changes").Range("a10:t200").AutoFilter Field:=4, Criteria1:=viewsection
Sheets("changes").Range("d8") = viewsection

no need to activate sheets or cells

this is even better

Me.Hide

With Sheets("changes")
'.Activate
.Range("a10:t200").AutoFilter Field:=4, Criteria1:=viewsection
.Range("d8") = viewsection
End With

please clarify what the problem is

kind regards,
Erik

EDIT: are you sure the code is triggered ?
is your button called "cmdslctadj" ?
to check if the code is running
start with
MsgBox "code will run now"

 

[llan_man]

Listbox problems

Hi

When I say it's stopped working I meant that the autofilter doesn't use the "viewsection" selection to sort. All I get is the full list of data.

When I replace viewsection with some text from the listbox the autofilter works which leads me to believe that the "viewsection" variable is not being recognised by the autofilter or it has nothing assigned to it. I'm wondering if i've set my listbox up right. as said earlier it is called "viewsection" and has a row source assigned to it of variables set up elsewhere within the workbook.


I used your code and it's much neater. Thank you.

Regards

Harv

 

[Norie]

Harv

I don't actually see anywhere in that code where you assign a value to viewsection.

What type of listbox do you have? Is it on a userform?

 

[llan_man]

Hi

Yes. Viewsection is the name of a listbox on a userform. It's used for users to select how they want to filter data. They simply select a text string from the list box and the macro is then supposed to filter it for them.

Thanks

Harv

 

[Norie]

Harv

Is the listbox on the userform you are hiding at the start of the code?

If it is then it's value won't be available to reference in the rest of the code.

 

[llan_man]

Listbox Problem

Norrie

I have moved the "Me.Hide" script to just before the "End Sub" and it's still doing the same.

harv

 

[Norie]

Try stepping through the code by setting a breakpoint (F9) and using F8.

 

[erik.van.geit]

It is clear that you're working on a userform from start since you used.
Me.hide

also it is clear, when you take the time to do a little test that hiding a form doesn't influence the different values of controls staying available

Private Sub CommandButton1_Click()
Me.Hide
MsgBox TextBox1
Unload Me
MsgBox TextBox1
End Sub

even unload seems not to clear the value

so the reason is somewhere else
hmm, are you sure you did write the name of the listbox correctly ?
verify in the properties

(putting Option Explicit on top of your modules would avoid this kind of typos)

if so, feel free to send me your workbook (delete sensitive data)
I'll take a look and report

best regards,
Erik