ask user to select a target sheet as an input for a macro

  • Thread starter Thread starter Yossi evenzur
  • Start date Start date
Y

Yossi evenzur

How or what is the vba syntax if i want the user to select a atrget sheet for
a macro as an input?
 
What about having your user kick off the macro from the sheet he/she wants
it to operate on? Then you can use ActiveSheet inside your macro to
reference it.

Rick
 
Another possibility... Execute this statement in your macro at the point you
want to get the sheet name...

Answer = InputBox("Tell me a sheet name")

and then reference that sheet in your code using this Worksheets(Answer). As
an example...

Worksheets(Answer).Range("D4").Value = "Hello"

You will probably need to put some error checking code in just in case your
user types the sheet name in wrong though.

Rick
 
Hi
It dosen't matter, the problem is that both the target and source sheets
have different names each time. in general i want to open an excel workbook
with two sheets (different names each time and no i need these names to stay
as they are) in the first sheet i add row and vlookup at the second sheet.
any better ideas?
 
Hi Rick
Please take a look at the following:
1)
Answer = InputBox("Tell me a sheet name")

'the Answer is"XXXX"

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[1]C,'"What do I put HERE ???"'!R2C1:R255C3,3,FALSE)"

2) why can't i simply (simply?) click on the sheet?
 
Dim wks as worksheet

set wks = nothing
on error resume next
set wks = application.inputbox _
(Prompt:="please click on a cell in the worksheet to use", type:=8).parent
on error resume next

if wks is nothing then
'user hit cancel, what should happen
else
msgbox wks.name
end if

You may want to consider creating a userform that displays the worksheet names
(in comboboxes??) and allow the user to select the sheet from that list.

If you want to try, take a look at the userform in Myrna Larson and Bill
Manville's compare program:

http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla
 
1) You would use this formula...

ActiveCell.FormulaR1C1 = "=VLOOKUP(R[1]C," & Answer &
"!R2C1:R255C3,3,FALSE)"

2) If you had them click on the sheet tab, then that sheet would be selected
and you would have the situation I mentioned in my previous reply, only
delayed slightly... ActiveSheet would tell you what sheet they were on. Here
is another thought... if you know they will have to change sheets to make
your macro work (that is, the look up data is never going to be on the page
they are on when the macro is launched, then perhaps you could make use of
the Workbook's SheetActivate event to get recognize the new sheet had been
selected and get the sheet name from its Sh argument?

Rick


Yossi evenzur said:
Hi Rick
Please take a look at the following:
1)
Answer = InputBox("Tell me a sheet name")

'the Answer is"XXXX"

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[1]C,'"What do I put HERE ???"'!R2C1:R255C3,3,FALSE)"

2) why can't i simply (simply?) click on the sheet?

Rick Rothstein (MVP - VB) said:
Another possibility... Execute this statement in your macro at the point
you
want to get the sheet name...

Answer = InputBox("Tell me a sheet name")

and then reference that sheet in your code using this Worksheets(Answer).
As
an example...

Worksheets(Answer).Range("D4").Value = "Hello"

You will probably need to put some error checking code in just in case
your
user types the sheet name in wrong though.

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top