Tech Support Guy banner
Status
Not open for further replies.
1 - 18 of 18 Posts

·
Registered
Joined
·
348 Posts
Discussion Starter · #1 ·
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
 

·
Trusted Advisor
Joined
·
6,911 Posts
List in the box, do you mean a user form?
 

·
Registered
Joined
·
348 Posts
Discussion Starter · #3 ·
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 :)
 

·
Trusted Advisor
Joined
·
6,911 Posts
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.
 

·
Registered
Joined
·
4,916 Posts
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
 

·
Registered
Joined
·
348 Posts
Discussion Starter · #7 ·
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

·
Registered
Joined
·
5,458 Posts
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.
 

·
Registered
Joined
·
348 Posts
Discussion Starter · #9 ·
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
 

·
Registered
Joined
·
5,458 Posts
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?
 

·
Registered
Joined
·
348 Posts
Discussion Starter · #11 ·
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
 

·
Registered
Joined
·
5,458 Posts
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?
 

·
Registered
Joined
·
348 Posts
Discussion Starter · #13 ·
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

·
Registered
Joined
·
348 Posts
Discussion Starter · #14 ·
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 ;)
 

·
Trusted Advisor
Joined
·
6,911 Posts
Try user form2 and see the code,
 

Attachments

·
Registered
Joined
·
348 Posts
Discussion Starter · #16 ·
Thanks Zack, Hans, Rollin
You just taught me how to use a userform. :up:
I’m very grateful :D

Thanks for your patients in all of my questions even though some seemed stupid.

I owe all of you a beer, or two


Mike
 

·
Registered
Joined
·
5,458 Posts
The "right" way to bring up a form is completely up to you. I usually don't link a control to a worksheet range, unless it's a very large list which I can't code. And even then, I'll probably use an initialize event to just grab the data from the worksheet and populate the form however it's desired. It makes it a little more portable IMHO. Of course with data it's grabbing from the worksheet, that doesn't always make it portable, but more habit for me.

The initialize event is like the workbook open event for the workbook. It runs when the userform initializes, or opens. So that is the perfect time to populate controls. You can even call that routine again in other code found in your userform if you want to reload your form to it's default state (of which you have programmed).

As for your third question, "I would like to know why I didn't have to have a code for each month," well that is because of the following code I posted on the GO button...
Code:
    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
You see, it loops through the listbox, finds the selected value, and then goes to the named range of that value. Two reasons this might bite you in the behind, 1) you set your listbox up as a multi-select listbox, in which case it will assume the first selected item is the one you want, or 2) there is no named range the same as the items in the listbox. In order to combat the second item, you could loop through all of the named ranges in the workbook and populate your listbox that way - which would be done in the initialize event. Of course, if you know there will always be those named ranges, then maybe it's not of concern for you. In any case, there is no error trapping for that.

If you have any additional questions, don't hesitate to ask!
 
1 - 18 of 18 Posts
Status
Not open for further replies.
Top