combo box linked to named range

[Rhino V]

I have a list of named ranges in a form combo box. The ranges are located
all over the workbook across 7 worksheets. How does one make Excel jump to
the named range once it is selected in the combo box? Your help is greatly
appreciated.

 

[Rhino V]

Maybe I should rephrase this: I have some named ranges that refer to specific
cells in my workbook. These names are listed in a form combo box. I tried
this code to make Excel jump to the selected named cell, but to no result:

Private Sub ComboBox1_Change()
If Not Me.ComboBox1.Value = "" Then
Range(Me.ComboBox1.Value).Select
End If
End Sub

Can anyone help me make this work? Thanx.

 

[Dave Peterson]

I'm guessing that this combobox is on a worksheet.

If that's the case and the ranges are on different sheets, then you can't select
a range on a sheet that's not active.

I'd use something like:

Option Explicit
Private Sub ComboBox1_Change()
Dim TestRng As Range

If Me.ComboBox1.Value <> "" Then
Set TestRng = Nothing
On Error Resume Next
Set TestRng = ThisWorkbook.Names(Me.ComboBox1.Value).RefersToRange
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "Design error--no range by that name in ThisWorkbook"
Else
Application.Goto TestRng ', Scroll:=True
End If
End If
End Sub

 

[Rhino V]

Thanx Dave. It works... sort of. Just have a few more questions to get this
all finally tweaked and perfectly working.

Not being completely proficient oin VBA, where does the "Option Explicit"
supposed to go? When I copy and pastyour code, it gets included in the
previous Sub (just after End Sub).

Also, when I type in the combo box, as soon as it finds a word that matches
(maybe in the first 2 letters) it automatically jumps to the named range
without giving the user a chance to even see what the word was. Can we
change this from a Change event to a Click event so that it will only launch
when the user clicks the whole word that was found on the list?

And after it jumps to the named range, what code can I add to take the focus
off the combo box so that it stops arbitrarily jumping to the last selected
range when you click somewhere else on the sheet?

Mind you, I feel like I'm pushing my luck, but you may be the only that can
help me get this done. And I truly appreciate your expertise and your
insight on this.

 

[Dave Peterson]

Option Explicit
goes at the top of the module (not just the top of any procedure).

It tells the compiler that you want to be forced to declare any variables that
you use.

Then problem code like this:
myCell1.value = mycelll.value + 1
won't be difficult to find.

Personally, I'd put a commandbutton near that combobox. I'd remove the code
from behind the combobox and use the "ok" button's code to do the work.
Sometimes, my typing isn't what it should be.

And I think that would make the other problem disappear (post back if it
doesn't).