List boxs

[neilb]

Hi

I have just strated trying to use VBA.

I am currently trying to use a list box in a userform. I have created a listbox with a list of choices in.(this has been done by using the property window and rowsource). Once a choice is selected in the listbox how do I get a linked different worksheet to open?

Many thanks in advance

Neil

[Audiojoe]

It'd be easier if you could do the listbox in VBA as part of a userform. Do you know how to do this, or do you need some help?

[neilb]

No

I don't know how to do that. I am just begining to use vba

[Audiojoe]

OK, just try this as an exercise in a new workbook and adapt it however you like.

First, click on VIEW>TOOLBARS>CONTROL TOOLBOX, and select the button icon, then draw yourself a button to trigger the listbox for now. You can decide on other ways of triggering it later, such as when you enter a certain cell, or when you open a certain sheet. Once you've drawn the button, double click on it. This takes you to the VB Editor

It should say this:

Private Sub CommandButton1_Click()

End Sub


Click in the line between the two and write:

Userform1.Show

so now it should look like this:

Private Sub CommandButton1_Click()
Userform1.Show
End Sub

Now click on INSERT>USERFORM and a blank userform will appear along with a toolbox. Select the Listbox icon from the toolbox and draw it onto the userform. When you have finished, select the command button icon and draw one of those on there as well.

OK, now you want to add some choices to your listbox, so double click on the userform itself, and this should appear:

Private Sub UserForm_Click()

End Sub

You need to change the word Click to Initialize. This is telling the userform what to do when it initializes. In the lines between the two again, type the following:

Listbox1.AddItem "A"
Listbox1.AddItem "B"
Listbox1.AddItem "C"

so now it should look like this:

Private Sub UserForm_Initialize()
Listbox1.AddItem "A"
Listbox1.AddItem "B"
Listbox1.AddItem "C"
End Sub

Now to get back to your userform, double click on Userform1 in the upper left hand side window. When you get back, double click the button you drew. You'll have this:

Private Sub CommandButton1_Click()

End Sub

Put in the following so it looks like this:

Private Sub CommandButton1_Click()
Worksheets("Sheet1").range("a1").value=listbox1
Unload Me
Sheets("sheet2").select
End Sub

This tells it that once you click the button it enters what you chose from the list into cell A1 on Sheet1. Then it opens another sheet. You can change this to whatever sheet it is you want to open

If you want more help, just shout me

Audiojoe
I was in nappies when I started writing this

[neilb]

Nice one - top bloke

Just one question ... Do I use a IF statement to get it to go the the right sheet depending on the choices?( there is 15 sheets for them to choose from)

Is it easily possible to get this userform to open when the spreadsheet opens

Neil

[Audiojoe]

Yes, if for example when you choose "A" from the listbox you want to go to Sheet3, then use this in the command button's click event:

If Listbox1.value = "A" then
Unload Me
Sheets("sheet3").select
End if

and so on...

Audiojoe

[neilb]

Excellent

Is it easy to get a userform to open when the workbook is opened?

Neil

[Audiojoe]

Yeah, just click on a cell, then press ALT+F11. When the VB Editor comes up, look in the upper left hand window and double click on This Workbook (if you have two or more open, you will have to find the right one!).

Now you should have a blank sheet. There are two little drop down boxes above it. From the left one select Workbook and from the right one select Open.

Now you should have this:

Private Sub Workbook_Open()

End Sub

In it you need to put:

Userform1.Show

So it looks like this:

Private Sub Workbook_Open()
Userform1.Show
End Sub


Audiojoe
Kumbayah to you all