I am trying to use a macro for an option button on my spreadsheet to have it
go to a specific cell within the currently selected range. On my worksheet
currently I have a user form which with the use of a code that goes directly
to the range of cells based apon the name that the user inputs. For example
if the name Brian Monaghan is entered on my user form the range
Brian_Monaghan is then selected on my spreadsheet titlted Employee Tracker.
Then after that selection is made I want the user to be able to go over to a
definition page where the various tasks are defined with option buttons for
them to click. For example if the user determines they have performed an
analytical they would click the analytics button and then be taken to that
cell within their specific range. The tasks are all grouped by their
corresponding range names.

Brian,

Perhaps along these lines - if the named ranges are on the same page...

Dim myCell As Range
Set myCell = Intersect(Range("Brian_Monaghan"), Range("Analytical"))

Or, if you are using variables:
Set myCell = Intersect(Range(VarName), Range(VarTask))

HTH,
Bernie
MS Excel MVP


"BrianMo" wrote in message
...
I am trying to use a macro for an option button on my spreadsheet to have it
go to a specific cell within the currently selected range. On my worksheet
currently I have a user form which with the use of a code that goes directly
to the range of cells based apon the name that the user inputs. For example
if the name Brian Monaghan is entered on my user form the range
Brian_Monaghan is then selected on my spreadsheet titlted Employee Tracker.
Then after that selection is made I want the user to be able to go over to a
definition page where the various tasks are defined with option buttons for
them to click. For example if the user determines they have performed an
analytical they would click the analytics button and then be taken to that
cell within their specific range. The tasks are all grouped by their
corresponding range names.

I see what you are saying with that Bernie there is just one problem the name
of the range such as Brian Monaghan will be dependent apon what the user
enters into the message box on the user form. So I would not be able to
enter in what the first range name as an absolute it needs to be the current
selection on the sheet since the following code selects that specific range
of the users name. For example this is the code I have to determine the range
selction for the user.

Private Sub Login_click()
With UserInterface
On Error Resume Next
Set rng = Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text)
If rng Is Nothing Then
MsgBox "Bad name, quitting"
Unload UserInterface
ThisWorkbook.Close Savechanges:=False
End If
End With
Worksheets("Employee Tracker").Unprotect Password:="testpass"
Range("A5:N25").Select
Selection.Locked = True
Application.Goto rng, True
Unload UserInterface
Call Macro3
End Sub



"Bernie Deitrick" wrote:

Brian,

Perhaps along these lines - if the named ranges are on the same page...

Dim myCell As Range
Set myCell = Intersect(Range("Brian_Monaghan"), Range("Analytical"))

Or, if you are using variables:
Set myCell = Intersect(Range(VarName), Range(VarTask))

HTH,
Bernie
MS Excel MVP


"BrianMo" wrote in message
...
I am trying to use a macro for an option button on my spreadsheet to have it
go to a specific cell within the currently selected range. On my worksheet
currently I have a user form which with the use of a code that goes directly
to the range of cells based apon the name that the user inputs. For example
if the name Brian Monaghan is entered on my user form the range
Brian_Monaghan is then selected on my spreadsheet titlted Employee Tracker.
Then after that selection is made I want the user to be able to go over to a
definition page where the various tasks are defined with option buttons for
them to click. For example if the user determines they have performed an
analytical they would click the analytics button and then be taken to that
cell within their specific range. The tasks are all grouped by their
corresponding range names.

Brian,

I guess you would have a textbox to get the task name (though I would use a Listbox for each of
these to allow selecting the value from a predefined list):

Something like this should work:

Set rng = Intersect(Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text), _
Worksheets("Employee Tracker").Range( _
.TextboxTask.Text))

Of course, error checking and all that would be required - one advantage of using listboxes....

HTH,
Bernie
MS Excel MVP


"BrianMo" wrote in message
...
I see what you are saying with that Bernie there is just one problem the name
of the range such as Brian Monaghan will be dependent apon what the user
enters into the message box on the user form. So I would not be able to
enter in what the first range name as an absolute it needs to be the current
selection on the sheet since the following code selects that specific range
of the users name. For example this is the code I have to determine the range
selction for the user.

Private Sub Login_click()
With UserInterface
On Error Resume Next
Set rng = Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text)
If rng Is Nothing Then
MsgBox "Bad name, quitting"
Unload UserInterface
ThisWorkbook.Close Savechanges:=False
End If
End With
Worksheets("Employee Tracker").Unprotect Password:="testpass"
Range("A5:N25").Select
Selection.Locked = True
Application.Goto rng, True
Unload UserInterface
Call Macro3
End Sub



"Bernie Deitrick" wrote:

Brian,

Perhaps along these lines - if the named ranges are on the same page...

Dim myCell As Range
Set myCell = Intersect(Range("Brian_Monaghan"), Range("Analytical"))

Or, if you are using variables:
Set myCell = Intersect(Range(VarName), Range(VarTask))

HTH,
Bernie
MS Excel MVP


"BrianMo" wrote in message
...
I am trying to use a macro for an option button on my spreadsheet to have it
go to a specific cell within the currently selected range. On my worksheet
currently I have a user form which with the use of a code that goes directly
to the range of cells based apon the name that the user inputs. For example
if the name Brian Monaghan is entered on my user form the range
Brian_Monaghan is then selected on my spreadsheet titlted Employee Tracker.
Then after that selection is made I want the user to be able to go over to a
definition page where the various tasks are defined with option buttons for
them to click. For example if the user determines they have performed an
analytical they would click the analytics button and then be taken to that
cell within their specific range. The tasks are all grouped by their
corresponding range names.

I know what you are saying about the list box I would prefer to use them as
well but I was given specific instructions to not use them on this project so
unfortunately I can't. The problem I am incurring here while your code will
get the desired result I want is it wont work within my worksheet since the
way the process works. For instance the workbook file is opened and the
userform appears with the first and last name text boxes then when the user
inputs their name and clicks login my code that i copied below runs and then
the range with that user's name is selected and everything else is locked.
So with that said I think I may have stumbled across any easier solution I
just can't figure out how to write the code to do so.

Can you tell me how to write a line for selecting a cell within the active
selection. For instance I want to have it so that when the user clicks on the
option button named analytics they are taken to the range analytics and only
their cell within their range is highlighted.

Thanks for all the help you have provided thus far.

"Bernie Deitrick" wrote:

Brian,

I guess you would have a textbox to get the task name (though I would use a Listbox for each of
these to allow selecting the value from a predefined list):

Something like this should work:

Set rng = Intersect(Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text), _
Worksheets("Employee Tracker").Range( _
.TextboxTask.Text))

Of course, error checking and all that would be required - one advantage of using listboxes....

HTH,
Bernie
MS Excel MVP


"BrianMo" wrote in message
...
I see what you are saying with that Bernie there is just one problem the name
of the range such as Brian Monaghan will be dependent apon what the user
enters into the message box on the user form. So I would not be able to
enter in what the first range name as an absolute it needs to be the current
selection on the sheet since the following code selects that specific range
of the users name. For example this is the code I have to determine the range
selction for the user.

Private Sub Login_click()
With UserInterface
On Error Resume Next
Set rng = Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text)
If rng Is Nothing Then
MsgBox "Bad name, quitting"
Unload UserInterface
ThisWorkbook.Close Savechanges:=False
End If
End With
Worksheets("Employee Tracker").Unprotect Password:="testpass"
Range("A5:N25").Select
Selection.Locked = True
Application.Goto rng, True
Unload UserInterface
Call Macro3
End Sub



"Bernie Deitrick" wrote:

Brian,

Perhaps along these lines - if the named ranges are on the same page...

Dim myCell As Range
Set myCell = Intersect(Range("Brian_Monaghan"), Range("Analytical"))

Or, if you are using variables:
Set myCell = Intersect(Range(VarName), Range(VarTask))

HTH,
Bernie
MS Excel MVP


"BrianMo" wrote in message
...
I am trying to use a macro for an option button on my spreadsheet to have it
go to a specific cell within the currently selected range. On my worksheet
currently I have a user form which with the use of a code that goes directly
to the range of cells based apon the name that the user inputs. For example
if the name Brian Monaghan is entered on my user form the range
Brian_Monaghan is then selected on my spreadsheet titlted Employee Tracker.
Then after that selection is made I want the user to be able to go over to a
definition page where the various tasks are defined with option buttons for
them to click. For example if the user determines they have performed an
analytical they would click the analytics button and then be taken to that
cell within their specific range. The tasks are all grouped by their
corresponding range names.

Brian,

Try something like this:

myCol = Application.Match(.TextboxTask.Text, Worksheets("Employee
Tracker").Range("HeaderRow"),False)
Set rng = Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text).EntireRow.Cells(1, myCol)
rng.Select 'Though there is rarely a need to actually select anything.....

There may be other ways to set the myCol variable as a result of the Task input...., but you should
get the idea.

HTH,
Bernie
MS Excel MVP


"BrianMo" wrote in message
...
I know what you are saying about the list box I would prefer to use them as
well but I was given specific instructions to not use them on this project so
unfortunately I can't. The problem I am incurring here while your code will
get the desired result I want is it wont work within my worksheet since the
way the process works. For instance the workbook file is opened and the
userform appears with the first and last name text boxes then when the user
inputs their name and clicks login my code that i copied below runs and then
the range with that user's name is selected and everything else is locked.
So with that said I think I may have stumbled across any easier solution I
just can't figure out how to write the code to do so.

Can you tell me how to write a line for selecting a cell within the active
selection. For instance I want to have it so that when the user clicks on the
option button named analytics they are taken to the range analytics and only
their cell within their range is highlighted.

Thanks for all the help you have provided thus far.

"Bernie Deitrick" wrote:

Brian,

I guess you would have a textbox to get the task name (though I would use a Listbox for each of
these to allow selecting the value from a predefined list):

Something like this should work:

Set rng = Intersect(Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text), _
Worksheets("Employee Tracker").Range( _
.TextboxTask.Text))

Of course, error checking and all that would be required - one advantage of using listboxes....

HTH,
Bernie
MS Excel MVP


"BrianMo" wrote in message
...
I see what you are saying with that Bernie there is just one problem the name
of the range such as Brian Monaghan will be dependent apon what the user
enters into the message box on the user form. So I would not be able to
enter in what the first range name as an absolute it needs to be the current
selection on the sheet since the following code selects that specific range
of the users name. For example this is the code I have to determine the range
selction for the user.

Private Sub Login_click()
With UserInterface
On Error Resume Next
Set rng = Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text)
If rng Is Nothing Then
MsgBox "Bad name, quitting"
Unload UserInterface
ThisWorkbook.Close Savechanges:=False
End If
End With
Worksheets("Employee Tracker").Unprotect Password:="testpass"
Range("A5:N25").Select
Selection.Locked = True
Application.Goto rng, True
Unload UserInterface
Call Macro3
End Sub



"Bernie Deitrick" wrote:

Brian,

Perhaps along these lines - if the named ranges are on the same page...

Dim myCell As Range
Set myCell = Intersect(Range("Brian_Monaghan"), Range("Analytical"))

Or, if you are using variables:
Set myCell = Intersect(Range(VarName), Range(VarTask))

HTH,
Bernie
MS Excel MVP


"BrianMo" wrote in message
...
I am trying to use a macro for an option button on my spreadsheet to have it
go to a specific cell within the currently selected range. On my worksheet
currently I have a user form which with the use of a code that goes directly
to the range of cells based apon the name that the user inputs. For example
if the name Brian Monaghan is entered on my user form the range
Brian_Monaghan is then selected on my spreadsheet titlted Employee Tracker.
Then after that selection is made I want the user to be able to go over to a
definition page where the various tasks are defined with option buttons for
them to click. For example if the user determines they have performed an
analytical they would click the analytics button and then be taken to that
cell within their specific range. The tasks are all grouped by their
corresponding range names.