vba Excel 提示用户输入要在宏中使用的数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11971307/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel prompt user for number to use in macro
提问by Taylor Zwick
I have a recorded macro to pull select fields from a web service, the URL is something like http://xxxx.com/reportviewer.aspx?ids=123456789012I would like to prompt the user to input that number as a variable and have the number be passed to the 2 locations were that number is used in the macro.
我有一个录制的宏来从 Web 服务中提取选择字段，URL 类似于http://xxxx.com/reportviewer.aspx?ids= 123456789012我想提示用户输入该数字作为变量并有传递到 2 个位置的数字是在宏中使用的数字。
I know that i have to create another macro to have the user input a value for after that I'm unsure of how pass that along to be filled in the correct location?
我知道我必须创建另一个宏才能让用户输入一个值，之后我不确定如何将其传递到正确的位置？
Here is my code so far
到目前为止，这是我的代码
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+r
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://xxxx.com/reportviewer.aspx?ids=123456789012", _
Destination:=Range("$A"))
.Name = "reportviewer.aspx?ids=123456789012"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "30,33,37,38,46"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Range("A2").Select
采纳答案by danielpiestrak
You can use InputBoxto collect the user's input.
After collecting, you can test their input before continuing to execute the code.
here's the input/validation example:
您可以InputBox用来收集用户的输入。收集后，您可以在继续执行代码之前测试他们的输入。这是输入/验证示例：
'-store user input in 'sUserInput' variable
Dim sUserInput As String
sUserInput = InputBox("Enter Number:", "Collect User Input")
'test input before continuing to validate the input
If Not (Len(sUserInput) > 0 And IsNumeric(sUserInput)) Then
MsgBox "Input not valid, code aborted.", vbCritical
Exit Sub
End If
Your code can reference the variable by refering to the sUserInputhere's a complete version:
您的代码可以通过参考sUserInput这里的完整版本来引用变量：
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+r
'
'-store user input in 'sUserInput' variable
Dim sUserInput As String
sUserInput = InputBox("Enter Number:", "Collect User Input")
'test input before continuing to validate the input
If Not (Len(sUserInput) > 0 And IsNumeric(sUserInput)) Then
MsgBox "Input not valid, code aborted.", vbCritical
Exit Sub
End If
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://network.construction.com/reportviewer.aspx?ids=" & sUserInput, _
Destination:=Range("$A"))
.Name = "reportviewer.aspx?ids=" & sUserInput
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "30,33,37,38,46"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
回答by perilbrain
You can do it easily
你可以轻松做到
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+r
'
Dim stpo
stpo = InputBox("Enter the report number")
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://network.construction.com/reportviewer.aspx?ids="&stpo, _
Destination:=Range("$A"))
