msoControlComboBox navigation

[mattpfc]

Hi all,


Could somebody let me now how I can call my navigate sub from the .OnAction line within my code attached please. Or is there another way I can navigate to specific cells on selection of a value from the combo box.

Private Sub Worksheet_Activate()

Set newcombo = CommandBars("Engineer Selection").Controls.Add(Type:=msoControlComboBox)
With newcombo
        .AddItem "Adams"
        .AddItem "Bezuidenhout"
        .AddItem "Bunce"
        .AddItem "Cambridge"
        .AddItem "Collins"
        .AddItem "Donnelly"
        .AddItem "Henry"
        .AddItem "Michna"
        .AddItem "Milnes"
        .AddItem "O'Connell"
        .AddItem "O'Sullivan"
        .AddItem "Spencer"
        .AddItem "Warren"
        .AddItem "Wright"
        .AddItem "Naeem"
        .AddItem "Other"
       .Style = msoComboNormal
        .OnAction = Navigate()
End With
End Sub
Private Sub Navigate()
Select Case newcombo.Text
Case "Adams"
Range("A8").Select
Case "Bezuidenhout"
Range("A43").Select
Case "Bunce"
Range("A75").Select
Case "Cambridge"
Range("A108").Select
Case "Collins"
Range("A141").Select
Case "Donnelly"
Range("A174").Select
Case "Donnelly"
Range("A174").Select
Case "Henry"
Range("A207").Select
Case "Michna"
Range("A240").Select
Case "Milnes"
Range("A273").Select
Case "O'Connell"
Range("A306").Select
Case "O'Sullivan"
Range("A339").Select
Case "Spencer"
Range("A372").Select
Case "Warren"
Range("A405").Select
Case "Wright"
Range("A439").Select
Case "Naeem"
Range("A473").Select
Case "Other"
Range("A508").Select
End Select
End Sub

 

[Juan Pablo González]

Name

A8 "Adams", A43 "Bunce", A75 "Cambridge", ..., A273 "OConnell" (Note here that there is NO apostrophe in the name of A273...), A306 "OSullivan", etc.

Then, change your Navigate code to this:

Private Sub Navigate() 
    Application.Goto Range(Application.Substitute(newcombo.Text, "'", "")), True
End Sub

 

[Juan Pablo González]

Oh, another thing that I just saw...

Change this:

.OnAction = Navigate()

to this

.OnAction = "Navigate"

and remove the "Private" from the definition of "Private Sub Navigate()"

and put the Navigate sub in a standard module as well.. I don't see where you define the 'newtext' variable... where do you do that ?

 

[mattpfc]

Thanks for the promt reply Juan, I am a little unclear of the advice you gave in your first post though

Name

A8 "Adams", A43 "Bunce", A75 "Cambridge", ..., A273 "OConnell" (Note here that there is NO apostrophe in the name of A273...), A306 "OSullivan", etc.

Then, change your Navigate code to this:

Any chance you could explain in a little more detail please[/quote]

 

[Juan Pablo González]

Select the cell A3, and in the name box (That white box to the left of the formula bar), type "Adams" without the quotes. Repeat that step with all the other cells...

 

[Andrew Poulsom]

Select the cell A3, and in the name box (That white box to the left of the formula bar), type "Adams" without the quotes. Repeat that step with all the other cells...

Just a caution - you must press Enter after typing the name, otherwise it won't stick.

 

[mattpfc]

Thanks again juan, but i seem to be having a problem with this line. I have named the cells but the n avigation does not work

Application.Goto Range(Application.Substitute(newcombo.Text, "'", "")), True

 

[Juan Pablo González]

Do you have a 'newcombo' variable defined somewhere ? anywhere ?

 

[Legacy 98055]

Try this. You have a private sub for "OnAction". You need to qualify where the "Navigate" procedure is. Hence, this:

.OnAction = ActiveSheet.CodeName & ".Navigate"

Private Sub Worksheet_Activate()
    Dim newcombo As CommandBarComboBox
    Set newcombo = CommandBars("Engineer Selection").Controls.Add(Type:=msoControlComboBox)
    With newcombo
        .AddItem "Adams"
        .AddItem "Bezuidenhout"
        .AddItem "Bunce"
        .AddItem "Cambridge"
        .AddItem "Collins"
        .AddItem "Donnelly"
        .AddItem "Henry"
        .AddItem "Michna"
        .AddItem "Milnes"
        .AddItem "O'Connell"
        .AddItem "O'Sullivan"
        .AddItem "Spencer"
        .AddItem "Warren"
        .AddItem "Wright"
        .AddItem "Naeem"
        .AddItem "Other"
        .Style = msoComboNormal
        .OnAction = ActiveSheet.CodeName & ".Navigate"
    End With
End Sub

Private Sub Navigate()
    Select Case CommandBars.ActionControl.Text
        Case "Adams"
        Range("A8").Select
        Case "Bezuidenhout"
        Range("A43").Select
        Case "Bunce"
        Range("A75").Select
        Case "Cambridge"
        Range("A108").Select
        Case "Collins"
        Range("A141").Select
        Case "Donnelly"
        Range("A174").Select
        Case "Donnelly"
        Range("A174").Select
        Case "Henry"
        Range("A207").Select
        Case "Michna"
        Range("A240").Select
        Case "Milnes"
        Range("A273").Select
        Case "O'Connell"
        Range("A306").Select
        Case "O'Sullivan"
        Range("A339").Select
        Case "Spencer"
        Range("A372").Select
        Case "Warren"
        Range("A405").Select
        Case "Wright"
        Range("A439").Select
        Case "Naeem"
        Range("A473").Select
        Case "Other"
        Range("A508").Select
    End Select
End Sub

 

[Legacy 98055]

Not allowed to edit post?

Note that this is different than your original code...

Select Case CommandBars.ActionControl.Text

Tom