User input into cell formula?

[StargateFan]

I have this script to ask user for a start date which works perfectly:
----------------------------------------------------------------------------------------------------------------------
Sub StartDate()

ActiveSheet.Unprotect 'place at the beginning of the code

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter the start date for this sheet that
you need for the 2-week on, 2-week off, Par-A-Gon schedule:" & vbCrLf
& vbCrLf & _
"(Excel is flexible; you can pretty much type any
date format and it'll know what date you mean!)", _
Title:="Overtime Start Date", _
Default:=Format(Date, "mmm dd, yyyy"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("A2").Value = Format(CDate(vResponse), "mmm dd, yyyy")

ActiveSheet.Protect ' place at end of code
End Sub
----------------------------------------------------------------------------------------------------------------------

What user input box would ask for dosage that could then be dumped
into a protected cell with this custom format (to be dumped into
protected cell E1):

"''Dose'' = "@" tablets in a.m. & p.m."


Thank you!

 

[GS]

StargateFan expressed precisely :

I have this script to ask user for a start date which works perfectly:
----------------------------------------------------------------------------------------------------------------------
Sub StartDate()

ActiveSheet.Unprotect 'place at the beginning of the code

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter the start date for this sheet that
you need for the 2-week on, 2-week off, Par-A-Gon schedule:" & vbCrLf
& vbCrLf & _
"(Excel is flexible; you can pretty much type any
date format and it'll know what date you mean!)", _
Title:="Overtime Start Date", _
Default:=Format(Date, "mmm dd, yyyy"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("A2").Value = Format(CDate(vResponse), "mmm dd, yyyy")

ActiveSheet.Protect ' place at end of code
End Sub
----------------------------------------------------------------------------------------------------------------------

What user input box would ask for dosage that could then be dumped
into a protected cell with this custom format (to be dumped into
protected cell E1):

"''Dose'' = "@" tablets in a.m. & p.m."


Thank you!

Use the same InputBox method as you did for getting the date. Just ask
for the number of tablets and check the user's input for
IsNumeric(vResponse). Then dump this into E1 as follows:

Range("E1").Value = "Dose = " & vResponse & " tablets in a.m. & p.m."

<FWIW>
As for the protect/unprotect that you're doing, it can be avoided by
setting the arg 'userinterfaceonly' to TRUE when protecting the sheet.
This will let you edit locked cells with code.

 

[StargateFan]

[SNIP]

Use the same InputBox method as you did for getting the date. Just ask
for the number of tablets and check the user's input for
IsNumeric(vResponse). Then dump this into E1 as follows:

Range("E1").Value = "Dose = " & vResponse & " tablets in a.m. & p.m."

<FWIW>
As for the protect/unprotect that you're doing, it can be avoided by
setting the arg 'userinterfaceonly' to TRUE when protecting the sheet.
This will let you edit locked cells with code.

Thanks much for this! I got the code from a kind helper a few years
back and have modified as needed in terms of information and such but
I don't really know how to change it so drastically. I did what I
know and guessed at some of the rest but here is what I have so far
(though it doesn't work <sheepish grin>):

===================================================================
Sub Dosage_UserInput()

ActiveSheet.Unprotect 'place at the beginning of the code

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="What are number of tablets to take in a.m.
and p.m.?" & vbCrLf & _
" (usually the same so not splitting)?:", _
Title:="Dosage ... ?", _
Default:=Format(Date, "#"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("E1").Value = Format(CDate(vResponse), "#")

Range("A3").Select

ActiveSheet.Protect ' place at end of code
End Sub
===================================================================

Thanks for the help!

D

 

[GS]

Try this...

Sub GetUserDosage()
Dim vAns As Variant, sMsg As String

'Construct the prompt message
sMsg = "What are number of tablets to take in a.m. and p.m.?"
sMsg = sMsg & vbCrLf
sMsg = sMsg & "(usually the same so not splitting)"

vAns = Application.InputBox(sMsg, "Enter Dosage", Type:=1)
If Not vAns = False Then
'Make sure code can edit protected cells
ActiveSheet.Protect Userinterfaceonly:=True

'Insert the input value
Range("E1") = "Dose = " & vAns & " tablets in a.m. & p.m."
End If
End Sub

Since you are using Excel's Inputbox method rather than VBA's Input
function, the result is evaluated for 'type' which if not correct then
the user will continue to be prompted to try again until a numeric
value is entered or the user cancels. In this case, wrapping this in a
Do...Loop isn't necessary.

Finally, if the user cancels then flow exits the sub. If we get a valid
input then code in the If construct executes.

 

[GS]

Just so you know.., the If construct could be written like this:

If vAns Then '//we got a valid input
'Make sure code can edit protected cells
ActiveSheet.Protect Userinterfaceonly:=True

'Insert the input value
Range("E1") = "Dose = " & vAns & " tablets in a.m. & p.m."
End If

I just used the Not operator for clarity, but it's really not necessary
since any validated return will not be FALSE.

 

[StargateFan]

Try this...

Sub GetUserDosage()
Dim vAns As Variant, sMsg As String

'Construct the prompt message
sMsg = "What are number of tablets to take in a.m. and p.m.?"
sMsg = sMsg & vbCrLf
sMsg = sMsg & "(usually the same so not splitting)"

vAns = Application.InputBox(sMsg, "Enter Dosage", Type:=1)
If Not vAns = False Then
'Make sure code can edit protected cells
ActiveSheet.Protect Userinterfaceonly:=True

'Insert the input value
Range("E1") = "Dose = " & vAns & " tablets in a.m. & p.m."
End If
End Sub

Since you are using Excel's Inputbox method rather than VBA's Input
function, the result is evaluated for 'type' which if not correct then
the user will continue to be prompted to try again until a numeric
value is entered or the user cancels. In this case, wrapping this in a
Do...Loop isn't necessary.

Finally, if the user cancels then flow exits the sub. If we get a valid
input then code in the If construct executes.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thank you! This seems to be working perfectly. Much more elegant
code.

The only thing I did was to take out the text in the answer since I
subsequently had to do calculations on the answer. I formatted the
cell as a custom one with the text added there. But it's really,
really good. Thanks!