Passing Strings from UserForm to Module

[John Kauffman]

Using VBA, I want to collect a range from the user with a UserForm, and then pass the range string to a subroutine in a module. I can pass numbers in this fashion, but I am unsuccessful passing strings. Any suggestions?

 

[Bob Phillips]

Rather than suggest a way, why don't you tell us how you pass numbers, as
the technique for strings should be similar.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Using VBA, I want to collect a range from the user with a UserForm, and

then pass the range string to a subroutine in a module. I can pass numbers
in this fashion, but I am unsuccessful passing strings. Any suggestions?

 

[Bernie Deitrick]

John,

Dimension a string as a public variable at the top of a codemodule:

Option Explicit
Public myStr As String

Sub TryNow()
Load UserForm1
UserForm1.Show
MsgBox myStr
Range(myStr).Select
End Sub

Put a RefEdit box on your userform - which is the best way to select a
range, and then use something like this with a commandbutton:

Private Sub CommandButton1_Click()
myStr = UserForm1.RefEdit1.Text
UserForm1.Hide
End Sub

HTH,
Bernie
MS Excel MVP

Using VBA, I want to collect a range from the user with a UserForm, and

then pass the range string to a subroutine in a module. I can pass numbers
in this fashion, but I am unsuccessful passing strings. Any suggestions?

 

[Tom Ogilvy]

Sub Main()
Dim sStr as String
sStr = "A1:B30"
mysub sStr
End Sub

Sub MySub(sStr1 as String)
msgbox Range(sStr1).Address(External:=True)
End Sub

--
Regards,
Tom Ogilvy

Using VBA, I want to collect a range from the user with a UserForm, and

then pass the range string to a subroutine in a module. I can pass numbers
in this fashion, but I am unsuccessful passing strings. Any suggestions?