searchable combobox multiple sheets

[mrCaptObvious]

hey y'all...

I am trying to use vba userform to create a searchable and editable database for my newly acquired telecom infrastructure.

what i am trying to accomplish: I have multiple sheets about 25 named "cable01 pairs 1-100, cable01 pairs 101-200, cable02 pair 1-100, cable02 pair 101-200...and so on. each sheet has 100 rows (pairs) with 11 columns in it.
I want to create 2 comboboxes. the first one to select the sheet itself as the first searchable field "Cable 01 pairs 1-100...etc." then, the second combobox displays my 100 rows of which one can be selected and When i click search, it will show the 11 fields associated with that combination of comboboxes in multiple text boxes that can be edited and updated on its respected sheet.
I can't upload my userform picture, excel on mac doesn't let me. Excel sheet is on my work computer.

 

[Saurabhj]

Hi,

Welcome to Mr. Excel board.

Check below code. I used only 2 text fields.

Option Explicit
Dim selSheet As Worksheet

Private Sub cboSheets_Change()
    cboItem.Clear
    Dim rowno As Integer
    Set selSheet = Worksheets(cboSheets.Value)
    For rowno = 2 To selSheet.Cells(Rows.Count, 1).End(xlUp).Row
        cboItem.AddItem selSheet.Range("A" & rowno)
    Next
End Sub

Private Sub cmdSearch_Click()
    Dim foundcell As Range
    If cboSheets.ListCount = 0 Or cboItem.ListCount = 0 Then Exit Sub
    With selSheet
        Set foundcell = .Cells.Find(What:=cboItem.Value, After:=.Cells(1, 1), _
        LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
       
        txtAge = .Range("B" & foundcell.Row)
        txtLocation = .Range("C" & foundcell.Row)
    End With
End Sub

Private Sub UserForm_Activate()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        cboSheets.AddItem ws.Name
    Next
End Sub

 

[mrCaptObvious]

Thank you very much. That did the trick. As a follow up: If I add a submit button, how can I add/update/ and save the workbook automatically with the newly entered text to the worksheet for the items i searched for from the comboboxes?
this is what i have so far...I've tried searching but only found discussions based on the same worksheet, not the workbook itself, or i'm not understanding the code. I am extremely new to this, trying to learn.

 

[Saurabhj]

Hi,

Please use below change.

1. declare foundcell variable at global level. (earlier it was declared inside Click event of Search button. remove that declaration)
2. Add below code for Submit button.

Dim foundcell As Range

Private Sub cmdSubmit_Click()
        If cboSheets.ListCount = 0 Or cboItem.ListCount = 0 Then Exit Sub
        With selSheet
            .Range("B" & foundcell.Row) = txtAge.Value
            .Range("C" & foundcell.Row) = txtLocation.Value
        End With
        MsgBox "Data updated", vbInformation
End Sub