Member Avatar for shabadoo

I'm am very new to vba for excel and having a hell of a time with it.

I am trying to create a program for small business that will be used by admin and managers to quote jobs, check/update work schedule, check inventory and such.

I have a main workbook containing multiple userforms for Login, and procedure choice to only allow individuals to perform chosen tasks.

I am trying to create a userform that includes several checkboxes, one for each possible task, a command button ("CmbIntroNext") and a command button ("CmbLogout"). Each task when chosen should open a different corresponding workbook when "CmbIntroNext" is clicked.

So far I have the following code:


Private Sub CmbIntroNext_Click()

If cmbCreateQuote <> True Then
Exit Sub
Else
frmIntroduction.Hide
frmQuote_1.Show
Exit Sub

If cmbCheckInventory <> True Then
Exit Sub
Else
frmIntroduction.Hide
Workbooks("Inventory.xlsm").Activate
Sheets("shInventory").Select
Range("A1").Select
Exit Sub

If cmbSchedule <> True Then
Exit Sub
Else
frmIntroduction.Hide
Exit Sub

If cmbUpdateSchedule <> True Then
Exit Sub
Else
frmIntroduction.Hide
End If

If cmbCreateInvoice <> True Then
Exit Sub
Else
frmIntroduction.Hide
Exit Sub

If cmbCreateInvoice <> True Then
Exit Sub
Else
frmIntroduction.Hide
Exit Sub
End If
End Sub

Private Sub CmbLogout_Click()

frmIntroduction.Hide
frmLogin.Show

End Sub

__________________________________________
The first block of If statement works without the rest because it requires an additional userform before entering a workbook but that's because this is as far as I have gotten so far.

The second block of If statement is where I am having a problem.
How do I get the corresponding "Inventory" workbook to open, activate and select a specific cell to start on.

Once I figure out how to do it for the second block, I will be able to modify the commands to the other checkboxes.

Please help!

  • 2 Contributors
  • 1 Reply
  • 128 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by AndreRet

Dani AI

Generated

A few quick, practical points based on 's post and 's suggestion: the current handler exits too early because each checkbox block contains an Exit Sub. That prevents any later code from running. Use either mutually exclusive logic (If … ElseIf …) when only one task can be chosen, or test each checkbox in sequence (no early exits) when multiple selections are allowed. The sample below shows a safe pattern to open (or attach to) the Inventory workbook, activate the sheet and select a start cell. Replace chkInventory with the actual checkbox name used on the form (for example the original cmbCheckInventory).

Private Sub CmbIntroNext_Click()
    Dim wb As Workbook
    Dim fn As String
    fn = ThisWorkbook.Path & "\Inventory.xlsm"    ' adjust path if needed

    If Me.chkInventory.Value = True Then
        If Not IsWorkbookOpen("Inventory.xlsm") Then
            Set wb = Workbooks.Open(fn)
        Else
            Set wb = Workbooks("Inventory.xlsm")
        End If
        Me.Hide
        wb.Activate
        wb.Sheets("shInventory").Range("A1").Select
    End If

    ' handle other checkboxes here (ElseIf for exclusive choices)
End Sub

Private Function IsWorkbookOpen(wbName As String) As Boolean
    Dim w As Workbook
    For Each w In Application.Workbooks
        If StrComp(w.Name, wbName, vbTextCompare) = 0 Then IsWorkbookOpen = True: Exit Function
    Next
End Function

Troubleshooting notes: ensure the file path and sheet name exactly match; use ThisWorkbook.Path for files in the same folder; confirm macro security/trust settings so Workbooks.Open can run; prefer referencing workbook/worksheet objects rather than relying on default ActiveWorkbook/ActiveSheet to avoid surprises. Official reference for opening workbooks: Workbooks.Open documentation.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.