Input Box Asking for Multiple Responses
 

Have the following code (which is called from another macro), which works
fine when I enter the ranges as noted; however, the ranges will change each
time and each should be based as an offset to a certain cell, such as
Range("AS9").End(xlDown).Offset(4, 2). However, when I try to use this
reference instead of "AU90" for instance, it will ask me for my 1st input,
then errors out at the 'Range(addr).Value = response' line with a 'Method
Range of Object - Global failed' message. Have tried all the variations I
can think of; can someone please advise what I'm doing wrong?

Sub MainProcedure()
LoadData "AU90", "Enter the term (in years) you assumed."
LoadData "AV90", "Enter scope."
LoadData "AW90", "Did you include price protect?"
End Sub

Sub LoadData(addr As String, msg As String)
Dim response As Variant, iR As Integer
While response = ""
response = InputBox(prompt:=msg)
If response = "" Then
iR = MsgBox("You must enter a response; please try again.")
End If
Wend
Range(addr).Value = response
End Sub

Input Box Asking for Multiple Responses
 

Hi Paige,

Try:

'=============
Public Sub LoadData(addr As String, msg As String)
Dim response As String

Do While response = ""
response = InputBox(prompt:=msg)
If response = "" Then
MsgBox ("You must enter a response; please try again.")
End If
Loop
Range(addr).Value = response
End Sub
'<<=============


---
Regards,
Norman


"Paige" wrote in message
...
Have the following code (which is called from another macro), which works
fine when I enter the ranges as noted; however, the ranges will change
each
time and each should be based as an offset to a certain cell, such as
Range("AS9").End(xlDown).Offset(4, 2). However, when I try to use this
reference instead of "AU90" for instance, it will ask me for my 1st input,
then errors out at the 'Range(addr).Value = response' line with a 'Method
Range of Object - Global failed' message. Have tried all the variations I
can think of; can someone please advise what I'm doing wrong?

Sub MainProcedure()
LoadData "AU90", "Enter the term (in years) you assumed."
LoadData "AV90", "Enter scope."
LoadData "AW90", "Did you include price protect?"
End Sub

Sub LoadData(addr As String, msg As String)
Dim response As Variant, iR As Integer
While response = ""
response = InputBox(prompt:=msg)
If response = "" Then
iR = MsgBox("You must enter a response; please try again.")
End If
Wend
Range(addr).Value = response
End Sub

Input Box Asking for Multiple Responses
 

Thanks, Norman; however, I still get the same error happening. I don't think
it likes how I've referred to the ranges in MainProcedu

Sub MainProcedure()
Dim HWInput1 As Range
Dim HWInput2 As Range
Dim HWInput3 As Range
Set HWInput1 = Worksheets("Hardware
Inventory").Range("AS9").End(xlDown).Offset(4, 2)
Set HWInput2 = Worksheets("Hardware
Inventory").Range("AS9").End(xlDown).Offset(4, 3)
Set HWInput3 = Worksheets("Hardware
Inventory").Range("AS9").End(xlDown).Offset(4, 4)

LoadData "HWInput1", "Enter the term."
LoadData "HWInput2", "Is scope included?"
LoadData "HWInput3", "Is it price protected?"
End Sub
"Norman Jones" wrote:

Hi Paige,

Try:

'=============
Public Sub LoadData(addr As String, msg As String)
Dim response As String

Do While response = ""
response = InputBox(prompt:=msg)
If response = "" Then
MsgBox ("You must enter a response; please try again.")
End If
Loop
Range(addr).Value = response
End Sub
'<<=============


---
Regards,
Norman


"Paige" wrote in message
...
Have the following code (which is called from another macro), which works
fine when I enter the ranges as noted; however, the ranges will change
each
time and each should be based as an offset to a certain cell, such as
Range("AS9").End(xlDown).Offset(4, 2). However, when I try to use this
reference instead of "AU90" for instance, it will ask me for my 1st input,
then errors out at the 'Range(addr).Value = response' line with a 'Method
Range of Object - Global failed' message. Have tried all the variations I
can think of; can someone please advise what I'm doing wrong?

Sub MainProcedure()
LoadData "AU90", "Enter the term (in years) you assumed."
LoadData "AV90", "Enter scope."
LoadData "AW90", "Did you include price protect?"
End Sub

Sub LoadData(addr As String, msg As String)
Dim response As Variant, iR As Integer
While response = ""
response = InputBox(prompt:=msg)
If response = "" Then
iR = MsgBox("You must enter a response; please try again.")
End If
Wend
Range(addr).Value = response
End Sub

Input Box Asking for Multiple Responses
 

Hi Page,

Thanks, Norman; however, I still get the same error happening.
I don't think it likes how I've referred to the ranges in MainProcedu

Try:

'=============
Public Sub LoadData(rng As Range, msg As String)
Dim response As String

Do While response = ""
response = InputBox(prompt:=msg)
If response = "" Then
MsgBox ("You must enter a response; please try again.")
End If
Loop
rng.Value = response
End Sub
'<<=============

'=============
Sub MainProcedure()
Dim HWInput1 As Range
Dim HWInput2 As Range
Dim HWInput3 As Range

Set HWInput1 = Worksheets("Hardware Inventory"). _
Range("AS9").End(xlDown).Offset(4, 2)
Set HWInput2 = Worksheets("Hardware Inventory"). _
Range("AS9").End(xlDown).Offset(4, 3)
Set HWInput3 = Worksheets("Hardware Inventory"). _
Range("AS9").End(xlDown).Offset(4, 4)

LoadData HWInput1, "Enter the term."
LoadData HWInput2, "Is scope included?"
LoadData HWInput3, "Is it price protected?"
End Sub
'<<=============

Note that I have changed:

Public Sub LoadData(addr As String, msg As String)

to

Public Sub LoadData(rng As Range, msg As String)

I have also changed:

LoadData "HWInput1", "Enter the term."
LoadData "HWInput2", "Is scope included?"
LoadData "HWInput3", "Is it price protected?"

to

LoadData HWInput1, "Enter the term."
LoadData HWInput2, "Is scope included?"
LoadData HWInput3, "Is it price protected?"

---
Regards,
Norman

Input Box Asking for Multiple Responses
 

Works like a charm! Thanks so much, Norman. Have a great day....

"Norman Jones" wrote:

Hi Page,

Thanks, Norman; however, I still get the same error happening.
I don't think it likes how I've referred to the ranges in MainProcedu

Try:

'=============
Public Sub LoadData(rng As Range, msg As String)
Dim response As String

Do While response = ""
response = InputBox(prompt:=msg)
If response = "" Then
MsgBox ("You must enter a response; please try again.")
End If
Loop
rng.Value = response
End Sub
'<<=============

'=============
Sub MainProcedure()
Dim HWInput1 As Range
Dim HWInput2 As Range
Dim HWInput3 As Range

Set HWInput1 = Worksheets("Hardware Inventory"). _
Range("AS9").End(xlDown).Offset(4, 2)
Set HWInput2 = Worksheets("Hardware Inventory"). _
Range("AS9").End(xlDown).Offset(4, 3)
Set HWInput3 = Worksheets("Hardware Inventory"). _
Range("AS9").End(xlDown).Offset(4, 4)

LoadData HWInput1, "Enter the term."
LoadData HWInput2, "Is scope included?"
LoadData HWInput3, "Is it price protected?"
End Sub
'<<=============

Note that I have changed:

Public Sub LoadData(addr As String, msg As String)

to

Public Sub LoadData(rng As Range, msg As String)

I have also changed:

LoadData "HWInput1", "Enter the term."
LoadData "HWInput2", "Is scope included?"
LoadData "HWInput3", "Is it price protected?"

to

LoadData HWInput1, "Enter the term."
LoadData HWInput2, "Is scope included?"
LoadData HWInput3, "Is it price protected?"

---
Regards,
Norman