Control or VBA programming a button
 

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
--
Message posted from http://www.ExcelForum.com

Control or VBA programming a button
 

Brian,

Good News: 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


"brianv" wrote in message
...
How do I create a button (say on sheet #3) that will change the incoming
data link from either sheet 1 or sheet 2

On sheet #3, I am importing data from either sheet #1 or #2 depending
on circumstances. Both sheet #1 and #2 are duplicate layouts, but
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 from
sheet 1 or 2.

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

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


See Temp file.

Thanks
BV

Attachment filename: temp.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=379324
---
Message posted from http://www.ExcelForum.com/

Control or VBA programming a button
 

Oops - your posted email didn't work. How do you want the file delivered?
"Alex J" wrote in message
.. .
Brian,

Good News: 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


"brianv" wrote in message
...
How do I create a button (say on sheet #3) that will change the incoming
data link from either sheet 1 or sheet 2

On sheet #3, I am importing data from either sheet #1 or #2 depending
on circumstances. Both sheet #1 and #2 are duplicate layouts, but
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 from
sheet 1 or 2.

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

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


See Temp file.

Thanks
BV

Attachment filename: temp.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=379324
---
Message posted from http://www.ExcelForum.com/

Control or VBA programming a button
 

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

Control or VBA programming a button
 

~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

--
Message posted from http://www.ExcelForum.com

Control or VBA programming a button
 

in B6 put the name Eng Est

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

produces 'Eng Est'!C11

note the single quotes to handle a name with spaces.

--
Regards,
Tom Ogilvy

"brianv" wrote in message
...
~x

I like your solution, learn something new everyday.

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


---
Message posted from http://www.ExcelForum.com/