Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Combining Macros for efficiency 1

Status
Not open for further replies.

Pudsters

Technical User
Mar 16, 2006
151
US
I have a userform with buttons that open up different workbooks, with the following macros. Could someone show me an example of how to combine them all into one macro for better code efficiency? Or is this the way to do it? I have several buttons similar to this on the form.

Private Sub cmdButton1_Click()
Workbooks.Open Filename:= _
"C:\File1.xls"
Unload Me
End Sub
------------
Private Sub cmdButton2_Click()
Workbooks.Open Filename:= _
"C:\file2.xls"
Unload Me
End Sub
-------------
Private Sub cmdButton3_Click()
Workbooks.Open Filename:= _
"C:\file3.xls"
Unload Me
End Sub
-------------

Thanks!
 
Use one listbox or combobox (to select file) and one commandbutton (to open selected item).
This method requires some validation, but you can benefit in case of variable number of files.

combo
 
Combo, I want to use a separate button for each file, simply for DESIGN purposes only.
 
Then you are stuck. Separate buttons have separate procedures. If Button1 opens FileA; and Button2 opens FileB, then...that is what you do.

You can't combine into one macro - unless you do it as combo suggests (which is frankly a good idea) - because they are separate instructions.

Look at what you have. ONE button opening ONE file.

Gerry
My paintings and sculpture
 
Okay, I thought I could by using If, Then statement or Case method, something like that...


Sub something here()
If button 1, open file 1,
If button 2, open file 2,
If button 3, open file 3,
and so on

End sub
 



There is no if test on an EVENT. Button_Click is an event. It happens, then it's gone, like a vapor.



Skip,

[glasses] [red][/red]
[tongue]
 
Which is the point.

Sub something here()
If button 1, open file 1,
If button 2, open file 2,
If button 3, open file 3,
and so on

End sub

Sure, you could write something kind of like that.

The questions are:

1. WHAT would fire Sub something here()????
2. WHAT does "button 1" mean? If button 1....what???? IF is a True or False logic statement. IF "whatever" evaluates a condition. It is either True, or it is False.

As Skip states, these are EVENTS.

Gerry
My paintings and sculpture
 
Pudsters,
If you really want to you could do it with a custom class. Here is an example:
John Walkenbach Handle Multiple UserForm Buttons With One Subroutine

I don't know if it will make things any faster, and for the example you provided I'm not sure it's worth it, but you asked.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)
 
CMP,

THAT IS IT! I have his book right in front of me and I remember reading that routine. It will work perfect for what I'm doing because I have 8 buttons on the form, each opens a different workbook.

Thanks!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top