Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that takes data entered in a user form textbox to go to a ra
I have a user form titled UserInterface with two text boxes one for the
user's first name and one for their last name. After they input their name for example Brian Monaghan and click on my command button titled "login" I want to have a macro that will take them to a range of cells named Brian_Monaghan. Then I want that same macro to be the case for every employee named entered who have corresponding ranges named after them as well. Which will then be linked to a macro I wrote earlier where a specific cell is clicked within that range and the rest of the cells on the worksheet not in that range are locked and protected via password. Here is the code I have for the first macro. It's very simple but I was going for a basic code here since this is a mock. Sheets("Employee Tracker").Select Range("A25:M25").Locked = False Range("A25:M25").Select ActiveSheet.Protect Password:="testpass" ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("C3:C25").Select Range("C25").Select Thanks in advance for any help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that takes data entered in a user form textbox to go to a ra
Look at
Private Sub Login_click() with UserInterface On error Resume Next set rng = Worksheets("names").Range( _ .TextboxFirst.Text & "_" & .TextboxLast.Text) On Error goto 0 if rng is nothing then msgbox "Bad name, quitting" unload UserInterface thisworkbook.Close Savechanges:=False End if End With application.Goto rng, true unload UserInterFace Call firstMacro End Sub -- Regards, Tom Ogilvy "BrianMo" wrote: I have a user form titled UserInterface with two text boxes one for the user's first name and one for their last name. After they input their name for example Brian Monaghan and click on my command button titled "login" I want to have a macro that will take them to a range of cells named Brian_Monaghan. Then I want that same macro to be the case for every employee named entered who have corresponding ranges named after them as well. Which will then be linked to a macro I wrote earlier where a specific cell is clicked within that range and the rest of the cells on the worksheet not in that range are locked and protected via password. Here is the code I have for the first macro. It's very simple but I was going for a basic code here since this is a mock. Sheets("Employee Tracker").Select Range("A25:M25").Locked = False Range("A25:M25").Select ActiveSheet.Protect Password:="testpass" ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("C3:C25").Select Range("C25").Select Thanks in advance for any help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that takes data entered in a user form textbox to go to
Thanks for the reply Tom I tried the code and got the following error message
"sub or object not defined". Do I need to enter in the specific name for my worksheet where it says set rng=Worksheets("names").Range( _ ? For example my worksheet is called Employee Task Tracker and do I need to input anything in the parantheses with the Range? I apologize for asking such questions but I am a real novice at Excel and am trying to learn how to read code. Thanks "Tom Ogilvy" wrote: Look at Private Sub Login_click() with UserInterface On error Resume Next set rng = Worksheets("names").Range( _ .TextboxFirst.Text & "_" & .TextboxLast.Text) On Error goto 0 if rng is nothing then msgbox "Bad name, quitting" unload UserInterface thisworkbook.Close Savechanges:=False End if End With application.Goto rng, true unload UserInterFace Call firstMacro End Sub -- Regards, Tom Ogilvy "BrianMo" wrote: I have a user form titled UserInterface with two text boxes one for the user's first name and one for their last name. After they input their name for example Brian Monaghan and click on my command button titled "login" I want to have a macro that will take them to a range of cells named Brian_Monaghan. Then I want that same macro to be the case for every employee named entered who have corresponding ranges named after them as well. Which will then be linked to a macro I wrote earlier where a specific cell is clicked within that range and the rest of the cells on the worksheet not in that range are locked and protected via password. Here is the code I have for the first macro. It's very simple but I was going for a basic code here since this is a mock. Sheets("Employee Tracker").Select Range("A25:M25").Locked = False Range("A25:M25").Select ActiveSheet.Protect Password:="testpass" ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("C3:C25").Select Range("C25").Select Thanks in advance for any help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that takes data entered in a user form textbox to go to a ra
Here is the code I have
Private Sub Login_click() With UserInterface On Error Resume Next Set rng = Worksheets("Names").Range( _ .TextboxFirst.Text & "_" & .TextBoxLast.Text) On Error GoTo 0 If rng Is Nothing Then MsgBox "Bad name, quitting" Unload UserInterface ThisWorkbook.Close Savechanges:=False End If End With Application.Goto rng, True Unload UserInterface Call FirstMacro End Sub "BrianMo" wrote: I have a user form titled UserInterface with two text boxes one for the user's first name and one for their last name. After they input their name for example Brian Monaghan and click on my command button titled "login" I want to have a macro that will take them to a range of cells named Brian_Monaghan. Then I want that same macro to be the case for every employee named entered who have corresponding ranges named after them as well. Which will then be linked to a macro I wrote earlier where a specific cell is clicked within that range and the rest of the cells on the worksheet not in that range are locked and protected via password. Here is the code I have for the first macro. It's very simple but I was going for a basic code here since this is a mock. Sheets("Employee Tracker").Select Range("A25:M25").Locked = False Range("A25:M25").Select ActiveSheet.Protect Password:="testpass" ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("C3:C25").Select Range("C25").Select Thanks in advance for any help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that takes data entered in a user form textbox to go to
Yes, when you don't provide specific information, I have to put in
placeholder values. So you would need to replace "Names" with the name of the sheet that actually contains the ranges for the name of the person. -- Regards, Tom Ogilvy "BrianMo" wrote: Thanks for the reply Tom I tried the code and got the following error message "sub or object not defined". Do I need to enter in the specific name for my worksheet where it says set rng=Worksheets("names").Range( _ ? For example my worksheet is called Employee Task Tracker and do I need to input anything in the parantheses with the Range? I apologize for asking such questions but I am a real novice at Excel and am trying to learn how to read code. Thanks "Tom Ogilvy" wrote: Look at Private Sub Login_click() with UserInterface On error Resume Next set rng = Worksheets("names").Range( _ .TextboxFirst.Text & "_" & .TextboxLast.Text) On Error goto 0 if rng is nothing then msgbox "Bad name, quitting" unload UserInterface thisworkbook.Close Savechanges:=False End if End With application.Goto rng, true unload UserInterFace Call firstMacro End Sub -- Regards, Tom Ogilvy "BrianMo" wrote: I have a user form titled UserInterface with two text boxes one for the user's first name and one for their last name. After they input their name for example Brian Monaghan and click on my command button titled "login" I want to have a macro that will take them to a range of cells named Brian_Monaghan. Then I want that same macro to be the case for every employee named entered who have corresponding ranges named after them as well. Which will then be linked to a macro I wrote earlier where a specific cell is clicked within that range and the rest of the cells on the worksheet not in that range are locked and protected via password. Here is the code I have for the first macro. It's very simple but I was going for a basic code here since this is a mock. Sheets("Employee Tracker").Select Range("A25:M25").Locked = False Range("A25:M25").Select ActiveSheet.Protect Password:="testpass" ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("C3:C25").Select Range("C25").Select Thanks in advance for any help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that takes data entered in a user form textbox to go to
Worked perfectly Tom thanks just one minor problem left but I think I may
know how to solve it. "Tom Ogilvy" wrote: Look at Private Sub Login_click() with UserInterface On error Resume Next set rng = Worksheets("names").Range( _ .TextboxFirst.Text & "_" & .TextboxLast.Text) On Error goto 0 if rng is nothing then msgbox "Bad name, quitting" unload UserInterface thisworkbook.Close Savechanges:=False End if End With application.Goto rng, true unload UserInterFace Call firstMacro End Sub -- Regards, Tom Ogilvy "BrianMo" wrote: I have a user form titled UserInterface with two text boxes one for the user's first name and one for their last name. After they input their name for example Brian Monaghan and click on my command button titled "login" I want to have a macro that will take them to a range of cells named Brian_Monaghan. Then I want that same macro to be the case for every employee named entered who have corresponding ranges named after them as well. Which will then be linked to a macro I wrote earlier where a specific cell is clicked within that range and the rest of the cells on the worksheet not in that range are locked and protected via password. Here is the code I have for the first macro. It's very simple but I was going for a basic code here since this is a mock. Sheets("Employee Tracker").Select Range("A25:M25").Locked = False Range("A25:M25").Select ActiveSheet.Protect Password:="testpass" ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("C3:C25").Select Range("C25").Select Thanks in advance for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
textbox on user form | Excel Programming | |||
User Form in VB = TextBox | Excel Discussion (Misc queries) | |||
Format data entered with User Form | Excel Programming | |||
Textbox trouble on a user form | Excel Programming | |||
User Form textbox formatting | Excel Programming |