pass a variable to a public sub

[Joanne]

I have this sub routine to get the value of fname:
Public Sub lbVendor_Click()
Dim fname As String, spath As String
Dim wb As Workbook, ws As Worksheet
Dim found As Boolean

With lbVendor
fname = .List(.ListIndex) 'capture the value of the list index#
If obHouse Then
spath = "C:\Pricing\House\"
ElseIf obDan Then
spath = "C:\Pricing\Dan\"
ElseIf obEdwin Then
spath = "C:\Pricing\Edwin\"
ElseIf obJeff Then
spath = "C:\Pricing\Jeff\"
ElseIf obJohn Then
spath = "C:\Pricing\John\"
End If
End With

Workbooks.Open spath & fname
End Sub

Now I need to get the value of fname into this sub routine so that I can
cycle thru a different wb to find the matching ws name and open and copy
it so that I can paste it as a new ws in the wb in the above routine:
Public Sub GetMstrWS()
Dim fname As String
Workbook.Open "C:\Pricing\Outlook Master Pricing.xls"

found = False
For Each ws In Worksheets
If ws.Name = fname Then
found = True
Exit For
End If
Next ws
If found = True Then
Worksheet.Copy 'copy the entire worksheet
End If
End Sub

BTW, all of the above coding is either thru the generous help of the
mvps in these forums or from reading thru the forums, for which I am
very grateful.

Could someone please help me get fname from Public Sub lbVendor_Click
into Public Sub GetMstrWS? I was thinking maybe these subs need to be
functions because I thought I read somewhere in the forums that you need
a function if you want to share variables, but I tried it and it didn't
work at all - (could be my coding - I'm not real good at it)

Thanks for your time and consideration
Joanne

 

[Jon Peltier]

It depends where you're calling GetMstrWS from. Generally you set up your
sub like this

Public Sub GetMstrWS(sName As String)
' code here which uses sName
End Sub

and you call it like this:

GetMstrWS sVariable

or

GetMstrWS "Name here"

- Jon

 

[Joanne]

Jon
Thanks for your reply.
But I don't understand really what you are telling me.
I am running the first sub to identify 'fname' and open the
corresponding workbook. This works just fine.

In another workbook, there is a worksheet with the same name as the
value of fname along with about 60 other ws that could be the match,
depending on what the user chooses as 'fname'.

What I need to do is loop thru the ws.names on the second wb until I
find the one that matches 'fname' from the first wb. But I don't know
how to tell the loop what the value of fname is since it is in a
different sub-routine.

Both of these sub-routines are running on the userform. I tried putting
the value of fname into the () in the name of the looping (second wb)
routine, but that gave me a global method error.
I don't know where to go from here.

 

[Jon Peltier]

If you are keeping the subs separate, then simply call the second sub from
the first:

Public Sub lbVendor_Click()
Dim fname As String, spath As String
Dim wb As Workbook, ws As Worksheet
Dim found As Boolean

With lbVendor
fname = .List(.ListIndex) 'capture the value of the list index#
If obHouse Then
spath = "C:\Pricing\House\"
ElseIf obDan Then
spath = "C:\Pricing\Dan\"
ElseIf obEdwin Then
spath = "C:\Pricing\Edwin\"
ElseIf obJeff Then
spath = "C:\Pricing\Jeff\"
ElseIf obJohn Then
spath = "C:\Pricing\John\"
End If
End With

Workbooks.Open spath & fname

GetMstrWS fName
End Sub


- Jon

 

[Joanne]

Thank you

If you are keeping the subs separate, then simply call the second sub from
the first:

Public Sub lbVendor_Click()
Dim fname As String, spath As String
Dim wb As Workbook, ws As Worksheet
Dim found As Boolean

With lbVendor
fname = .List(.ListIndex) 'capture the value of the list index#
If obHouse Then
spath = "C:\Pricing\House\"
ElseIf obDan Then
spath = "C:\Pricing\Dan\"
ElseIf obEdwin Then
spath = "C:\Pricing\Edwin\"
ElseIf obJeff Then
spath = "C:\Pricing\Jeff\"
ElseIf obJohn Then
spath = "C:\Pricing\John\"
End If
End With

Workbooks.Open spath & fname

GetMstrWS fName
End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______