Offset selection in VBA

[Adamhumbug]

Hi All,

I have a project and have populated a combobox.

When a combobox item is clicked, it is selected in the worksheet.

I would like to offset this selection so that i can can select other relevant data to fill a listbox.

This may sound confusing so i will try to explain. The user picks what menu they want to use whether it be dinner menu 1, dinner menu 2, conf menu 1 and so on. If they pick dinner menu 1 it will be selected in the work sheet. The actual menu items are in the column to the right. I am wanting to select all of the contents in this cell up until a blank space. I then want this newly selected information to fill a listbox.

Here is the code i have so far which is not much

Private Sub ComboBox1_Change()
Dim Choice As String
Dim InRow As Integer

Choice = Me.ComboBox1


Sheets("Menu_Name").Select

Cells.Find(What:=Choice, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Select

End Sub



Private Sub UserForm_Initialize()
Dim WS As Worksheet
Dim LastRow As Long
Dim aCell As Range


Set WS = ActiveSheet


With WS
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    
    For Each aCell In .Range("B2:B" & LastRow)
   
        If aCell.Value <> "" Then
            Me.ComboBox1.AddItem aCell.Value
        End If
    Next
End With
End Sub

 

[oldbrewer]

can you not just use hyperlinks to the various menus (need a return to home link on each menu)

 

[Adamhumbug]

can you not just use hyperlinks to the various menus (need a return to home link on each menu)

Hi, no i don't think this will do what i am after as i need to make a selection based on what is in the combobox. The information that it picks may change size (may be over more rows)

 

[oldbrewer]

ok - its just that I once designed a quality manual where the paragraph headings were on one page and clicking on them jumped you to more detail and explanation. I left spare rows so that info could be updated.