userform variable as global

[crew3407]

Hi,

I have a userform with a field for first name and a field for last nam
and an OK command button that executes the macro.

I store the last name and first name into variables and i want to b
able to use them in other procedures. for example

sub Ok_Click()

dim first as string
dim last as string

first = First.Text
last = Last.Text

ActiveSheet.Range("A1").Value = first & " " & last
Call upper

End sub

then in a module

sub upper()
ActiveSheet.Range("A2").Value = UCase(last)
end sub

When i call the upper procedure from the ok_click procedure, it doesn'
work. this is probably very dumb, but any help would be appreciated.
thanks

 

[Bob Phillips]

You have declared first and last as procedure level variables, so as soon as
the proc ends, they are gone. You also need to reference the form class.
Try

dim first as string
dim last as string

sub Ok_Click()

first = First.Text
last = Last.Text

ActiveSheet.Range("A1").Value = first & " " & last
Call upper

End sub

then in a module

sub upper()
ActiveSheet.Range("A2").Value = UCase(Userform1.last)
end sub



--

HTH

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

 

[crew3407]

thank you once agai

 

[crew3407]

actually, i'm having another problem.

even when i reference the form class, there is an error message tha
pops up.

Compile Error: Method or data memeber not found.

What am i doing wrong

 

[Bob Phillips]

Have you got the correct form name. I used Userform1 as that is the default,
but if you renamed it, you need to use that name.

--

HTH

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

 

[crew3407]

yes, I changed the userform name to client, so i used client.las

 

[Bob Phillips]

Can u post all the code then?

--

HTH

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

 

[crew3407]

Client (Userform) Code

Sub Ok_Click()

first = First.Text
last = Last.Text

'Then I call a procedure that creates a new sheet

Call NewImport

ActiveSheet.Range("A1").Value = first & " " & last
oldInitial = Left(Range("A1"),1)

For i = 11 To Worksheets.count
If UCase(last) = Worksheets(i).Name Then
ActiveSheet.Name = UCase(last) & ", " & UCase(newInitial)
Worksheets(i).Activate
existFirst = Left(Range("A1"), [Search(" ",A1,1)] -1)
newnamehyp = Worksheets(i).Name & ", " & _
UCase(existFirst)
newInitial = Left(Range("A1"), 1)
Worksheets(i).Name = UCase(last) & ", " & _
UCase(newInitial)
Call TrackList
End If
Next i

End Sub
(Above is an abbreviated version)

Different Module

Sub TrackList()

Worksheets("TRACK LIST").Activate
Columns("A:A").Select
Selection.Find(What:=UCase(last) <---this is where i get the problem
the rest is just the recorded macro for 'find').Select
Selection.Hyperlinks(1).SubAddress = " ' " & UCase(last) & _
", " & newInitial & "." & " '!A1"
Selection.Hyperlinks(1).TextToDisplay = newnamehyp

End Sub

---So I've put the variable declarations outside ok_click, but stil
there were problems. I hope this is enough. Thanks a bunch, it'l
clean up my code a lot. And hopefully the code that i have isn't to
bad either, heh. Thanks

 

[crew3407]

i got it...i just declared my vars

public last as string

thanks for all the help