Prompt User for Input to put in Cell A1

  • Thread starter Thread starter Tami
  • Start date Start date
T

Tami

Can someone help me write a macro that prompts the User for "Enter the Week
Name" (e.g. May Week 3) then put that response "May Week 1" in cell A1 on a
tab named "Projection" then returns the curser back to the first tab named
"Macros".
thx
 
'NO selections so you don't need to go back. fire from anywhere in the
workbook

sub getinfoforcell()
sheets("Projection").range("a1")=inputbox("Enter the week name ie: Week 3")
end sub
 
Don Guillett said:
sub getinfoforcell()
  sheets("Projection").range("a1")=inputbox("Enter the week name ie: Week 3")
end sub
Click to expand...
....

The following may be overengineering, but it keeps trying to get user
entry until the user confirms they really don't want to make an entry,
it provides a reasonable default value, and it doesn't clear the cell
when the user cancels the dialog(s).


Sub getinfoforcell()
Dim dr As String, resp As Variant, tc As Range

Set tc = Sheets("Sheet1").Range("A1")

dr = tc.Value2
If dr = "" Then resp = Format(Now + 7, "mmmm ""week #""")

Do
resp = InputBox(Prompt:="Enter week name:", Default:=dr)
If resp = "" Then _
resp = MsgBox(Prompt:="Try again?", Buttons:=vbOKCancel)
Loop Until resp <> vbOK

If resp <> vbOK Then tc.Value2 = resp

End Sub
 
Back
Top