userform variable as global
 

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

--
Message posted from http://www.ExcelForum.com

userform variable as global
 

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 " wrote in message
...
Hi,

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

I store the last name and first name into variables and i want to be
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't
work. this is probably very dumb, but any help would be appreciated.
thanks.


---
Message posted from http://www.ExcelForum.com/

userform variable as global
 

thank you once agai

--
Message posted from http://www.ExcelForum.com

userform variable as global
 

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

--
Message posted from http://www.ExcelForum.com

userform variable as global
 

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 " wrote in message
...
actually, i'm having another problem.

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

Compile Error: Method or data memeber not found.

What am i doing wrong?


---
Message posted from http://www.ExcelForum.com/

userform variable as global
 

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

--
Message posted from http://www.ExcelForum.com

userform variable as global
 

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 " wrote in message
...
yes, I changed the userform name to client, so i used client.last


---
Message posted from http://www.ExcelForum.com/

userform variable as global
 

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

--
Message posted from http://www.ExcelForum.com

userform variable as global
 

i got it...i just declared my vars

public last as string

thanks for all the help

--
Message posted from http://www.ExcelForum.com