Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup with multiple responses | Excel Discussion (Misc queries) | |||
How do I get multiple Vlookup responses? | Excel Worksheet Functions | |||
Multiple responses for a column | Excel Discussion (Misc queries) | |||
no responses... | Excel Discussion (Misc queries) | |||
Calculate multiple results from multiple input values? | Excel Discussion (Misc queries) |