Control or VBA programming a button

[brianv]

How do I create a button (say on sheet #3) that will change the incomin
data link from either sheet 1 or sheet 2

On sheet #3, I am importing data from either sheet #1 or #2 dependin
on circumstances. Both sheet #1 and #2 are duplicate layouts, bu
different data.


ex: (assume cell info)

From Sheet #1:
Item Est Qty: Manuf. Model Description
1 3 ABC 1234-AB Widget
2 4 ABC 1-234-DF Nugget
3 6 ABC 237-TD Contraption


From Sheet #2:
Item Est Qty: Manuf. Model Description
1 4 ACME 99-874 Really big widget
2 2 ACME 98-564 Really big Nugget
3 1 ACME 96-226 Really small Contraption

and by clicking the button, sheet #3 will either import the data fro
sheet 1 or 2.

I consider myself very proficient in excell formula's but I have no
done control or VBA Programming.

In addition is it possible that the button will change its labe
depending on which data is being utilized?


See Temp file.

Thanks
B

Attachment filename: temp.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=37932

 

[Alex J]

Brian,

Good You don't need a button!

I have written the variable link for you on sheet3 using the Indirect
spreadsheet function.
As for a button-like function, I have given you a drop-down sheet selector
using validation in named range "SheetSelect" (D5) with a list of source
spreadsheets to choose from.

If you really want a button, that can be added to perform the function of
the dropdown sheet selector.

I will email the attachment seperately.

Alex J

 

[Alex J]

Oops - your posted email didn't work. How do you want the file delivered?

Brian,

Good You don't need a button!

I have written the variable link for you on sheet3 using the Indirect
spreadsheet function.
As for a button-like function, I have given you a drop-down sheet selector
using validation in named range "SheetSelect" (D5) with a list of source
spreadsheets to choose from.

If you really want a button, that can be added to perform the function of
the dropdown sheet selector.

I will email the attachment seperately.

Alex J

 

[Guest]

All of thats quite possible.

to change the label on the button you'd just have the following code:
CommandButton1.Caption = "New Name"

CommandButton1 is the name of the button you've currently placed on that sheet

I'm not quite sure I understand the rest of your question? - do you want the macro to change the formula to refer to the other sheet? in which case you could just have the formula look like this =INDIRECT("Sheet"&B6&"!C11") with the cell B6 being the number 1 or 2 and therefore pointing the data to sheet 1 or 2

 

[brianv]

~x

I like your solution, learn something new everyday.

A further issue is when the sheet# are replaced with names. I having
little difficulty making the translation between ("sheet"&B6&"!C11")b
replacing "sheet"&B6&" with the sheet label. Sheet 1 is labeled Sale
Est, sheet 2 is labeled Eng Est

 

[Tom Ogilvy]

in B6 put the name Eng Est

=Indirect("'" & B6 & "'!C11")

produces 'Eng Est'!C11

note the single quotes to handle a name with spaces.