Solved: list box selection to run macro

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

mrl

Thread Starter
Joined
Aug 12, 2007
Messages
342
Hi,
I'm trying to have a box come up with a list and depending of the selection, run a macro. I have the following, but I'd like to have the list in the box instead of on the sheet. I'm new to boxes, so I hope I'm using the right terminology

HTML:
Sub test()
On Error Resume Next
InputCells = Application.InputBox(Prompt:="What Month", Type:=2)
   If InputCells = vbNullString Then
    Exit Sub
    Else
    Select Case InputCells
    Case "January"
    Janu
    Case "February"
    Febu
End Select
End If
End Sub



Sub Janu()
Application.Goto Reference:="january"
End Sub



Sub Febu()
Application.Goto Reference:="february"
End Sub
Mike
 

mrl

Thread Starter
Joined
Aug 12, 2007
Messages
342
Hi,
First, happy New Year
I know nothing about user forms
Way back in my Lotus days, I could have a box come up with choices. (it was called {menubranch}) When I clicked one, a macro associated with it would run
With the code that I showed you, the list is on the spreadsheet and when I click on a choice, the item goes into the box,or I type it myself into the box then the macro runs. I was trying to have the choices in the box. This would give me another learning tool. :D
If what I have to use is a user form, I'll have to learn about them.

Mike :)
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Hi Mike, happy New Year to you too.
I think I know what you mean.

I'll sleep over it. It's bedtime here now. If you have a simple sample you're using then attach it, okay?

Read it tomorrow.
 

mrl

Thread Starter
Joined
Aug 12, 2007
Messages
342
Hi, Hans
Happy dreams :D

I attached a sample file. Rollin_again helped me with the code (Thank you Rollin_again, I'm greatful) awhile back.


Mike
 

Attachments

Joined
Sep 4, 2003
Messages
4,912
As long as you can get the correct text to appear in your specific cell you can use an embedded button on the worksheet to fire the macro code. You can even get the code to fire automatically anytime the value changes in a designated cell. The other option is to create your own userform which can be displayed with a listbox to make your selection from and then a button to submit which will fire the code.

Open the workbook and press ALT + F11 to open the VB editor. You can then click INSERT >> USERFORM to create the blank form. You can resize the form by dragging the corners and and you should see a control box appear next to the form that will allow you to add whatever controls you want (textboxes, listboxes, buttons, labels, etc.) Play around with the form and put something together and we can tell you how to add the code.


Rollin
 

mrl

Thread Starter
Joined
Aug 12, 2007
Messages
342
Hi Rollin
How's the weather? We had about 4" of white stuff (lower Mich) Tues and Weds
I played :D with the user form and was able to get the contol buttons to work (calling up a macro)
I can't get the list box to do the same thing. I'm attaching a sample file
Thanks in advance for all the help :D
 

Attachments

Joined
Jul 25, 2004
Messages
5,458
Do you want a specific macro to run based on the selection, or would it be preferred to have one generic macro run where you pass a variable to it from the selection? The benefit this would have is you wouldn't have to have a bunch of macros to run based on the selection, you'd only need one.
 

mrl

Thread Starter
Joined
Aug 12, 2007
Messages
342
Hi Zack

If I highlight January, and hit OK I want to goto Range name "January"..If I highlight February, and hit OK I want to goto Range name "February", Etc. I Already figured out how to do it will command buttons in the userform (userform1 on the sample sheet). Now I'm trying to learn how to do it with a list box in the userform. (userform2)



Mike
 
Joined
Jul 25, 2004
Messages
5,458
Well this would be the basics...

Code:
Option Explicit

'This is the GO button
Private Sub CommandButton1_Click()
    Dim i As Long
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) = True Then
            Application.Goto (Me.ListBox1.List(i))
            Exit For
        End If
    Next i
End Sub

'This is just code for me to temporarily load a listbox with all month names
Private Sub UserForm_Initialize()
    Dim i As Long
    For i = 1 To 12
        Me.ListBox1.AddItem MonthName(i, False)
    Next i
End Sub

'This is the cancel button
Private Sub CommandButton2_Click()
    Unload Me
End Sub
Is this what you're looking for?
 

mrl

Thread Starter
Joined
Aug 12, 2007
Messages
342
Being that I don't know anything about userForms, I hope you don't mind my questions ;)
I was using the following code to bring up the userform (userform2 in the sample)
HTML:
Sub form2()
UserForm2.Show
End Sub
I put your codes in the properties section of UserForm2 after I clicked on that userform in the VBA window, and I deleted what I was trying to get to work. Now when I try to use my code to bring up the user form, I get an error message. something is wrong with "Userform2.show".
I have code for each name (month) in a module1.
Am I putting your code in the wrong place?
I don't know how to get the userform to show without writing code to show it
I don't know how to use the ....Sub UserForm_Initialize()
I don't know how make any of the "Private Sub....codes to work


I started out making command buttons on the sheet but I'm trying to make it easier.
I then used Rollin's response and was able to make command buttons on the userform and have them work'
Now I'm trying to learn how to make a list on the userform to start a macro depending on the choice
 
Joined
Jul 25, 2004
Messages
5,458
If you put the code in your userform's code module (of UserForm2), you would need to make sure and either change the code sub routines names to match your controls, or change your controls names to match that of the code. So the 'GO' button in my code is the CommandButton1, and the 'Cancel' button is the CommandButton2. The userform initialize code is generic and will fit in any userform code module, but you can only have one per module. The only other thing you would need to check is the listbox name. I used the generic name, ListBox1. The code I posted doesn't need a routine for each month. Is there any way you can upload your file? Or strip it of any sensitive data and upload it?
 

mrl

Thread Starter
Joined
Aug 12, 2007
Messages
342
I made up a sample sheet with a short List for the list box

I put your code in my userform's code module (of UserForm2), I changed my names to match yours. I left Userform1 in the spreadsheet so you can see if I have to tweek it. It works though :)

I had to make a command button to bring up userform1 and userform2 because I don't know how to do it any other rway.

Before I put in Private Sub UserForm_Initialize() etc. The command button for Userform2 would bring up the userform. Now it doesn't I probiably did something wrong. I don't know how to use it, either :confused:
 

Attachments

mrl

Thread Starter
Joined
Aug 12, 2007
Messages
342
Hey Zack

After I sent you the last reply, I took out the Private Sub UserForm_Initialize() thing and the userform worked like a charm :D :D

I still would like to know the right way to bring up the form
I would like to learn how to use the Initialize thing
I would like to know why I didn't have to have a code for each month
I know...too many questions ;)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top