Userform copying record to worksheet selected from combobox

[CutterJaxx]

Hi all,

Just a preface, as with so many I am new to VBA coding and will appreciate any constructive criticism/suggestions.

I have a user form which includes a combo box which is populated with a list of worksheets in the workbook. Only worksheets without the number 1 in cel BB are included in this list. The combo box is used to select the worksheet that the record being entered is to be inserted on. The user form is called by a macro assigned to an autoshape on a worksheet set up as a menu.

The issue is that the record entered on the userform is being inserted on the menu sheet(or which ever sheet is current at the time of calling the userform), not the sheet selected from the combobox.

Below is the code for the userform.

Any any help greatly appreciated.

Cutter

Private Sub cmdCancel_Click()

    Unload Me

End Sub

Private Sub cmdClearForm_Click()

    ComboBox1.Value = ""

    Call UserForm_Initialize

End Sub

Private Sub cmdOK_Click()
     
    Dim stWs As String, lrow As Long
    Dim ws As Worksheet
     
    stWs = Me.ComboBox1.Text
    Set ws = ThisWorkbook.Worksheets(stWs)
    
    With ws
    
        Range("B11").Select

        Do

        If IsEmpty(ActiveCell) = False Then

            ActiveCell.Offset(1, 0).Select

        End If

        Loop Until IsEmpty(ActiveCell) = True

        ActiveCell.Value = txtItemNo.Value
        ActiveCell.Offset(0, 1) = txtItemDescription.Value
        ActiveCell.Offset(0, 2) = txtPhoto1.Value
        ActiveCell.Offset(0, 3) = txtPhoto2.Value
        ActiveCell.Offset(0, 4) = txtPhoto3.Value
        ActiveCell.Offset(0, 5) = txtPhoto4.Value
        ActiveCell.Offset(0, 7) = txtDateRaised.Value
        ActiveCell.Offset(0, 11) = txtRemarks.Value

        Range("B11").Select
    
    End With
    
        Call UserForm_Initialize

End Sub


Private Sub ListBox1_Click()

End Sub

Private Sub ComboBox1_Change()

End Sub

Private Sub UserForm_Initialize()
     'add all the sheets in the workbook to the form combo
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        'criteria for worksheet to be added to list
        If ws.[BB1] <> 1 Then ComboBox1.AddItem ws.Name
    
    Next
    
    txtItemNo.Value = ""
    txtDateRaised.Value = ""
    txtItemDescription.Value = ""
    txtPhoto1.Value = ""
    txtPhoto2.Value = ""
    txtPhoto3.Value = ""
    txtPhoto4.Value = ""
    txtRemarks.Value = ""
    txtItemNo.SetFocus

End Sub

 

[PHV]

One way:

Private Sub cmdOK_Click()

    Dim stWs As String, lrow As Long
    Dim ws As Worksheet

    stWs = Me.ComboBox1.Text
    Set ws = ThisWorkbook.Worksheets(stWs)

    With ws
        lrow = 11
        Do
            If IsEmpty(.Cells(lrow, 2) = False Then
                lrow = lrow + 1
            End If
        Loop Until IsEmpty(.Cells(lrow, 2) = True

        .Cells(lrow, 2) = txtItemNo.Value
        .Cells(lrow, 3) = txtItemDescription.Value
        .Cells(lrow, 4) = txtPhoto1.Value
        .Cells(lrow, 5) = txtPhoto2.Value
        .Cells(lrow, 6) = txtPhoto3.Value
        .Cells(lrow, 7) = txtPhoto4.Value
        .Cells(lrow, 8) = txtDateRaised.Value
        .Cells(lrow, 9) = txtRemarks.Value

        .Cells(11, 2).Select

    End With

    Call UserForm_Initialize

End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CutterJaxx]

Many thanks PHV,

Apart from a couple of missing brackets on the following lines

        Do
            If IsEmpty(.Cells(lrow, 2)) = False Then
                lrow = lrow + 1
            End If
        Loop Until IsEmpty(.Cells(lrow, 2)) = True

and having to remove the . from in front of the following line

        .Cells(11, 2).Select

It works perfectly.

Thanks again
Cutter

 

[SkipVought]

and having to remove the . from in front of the following line

        .Cells(11, 2).Select

I don't think so!!! That range select, references ThisWorkbook.Worksheets(stWs).

If you want ANOTHER worksheet instead, then it's not just a matter of removing the DOT, although it MIGHT work under certain conditions. Rather, it is a matter of referencing the DESIRED WORKSHEET.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CutterJaxx]

Hi SkiVought,

I actually ended up removing that line completley, it was not required.

And yes when I removed the dot it referenced the original sheet not the sheet selected from the combobox.

Thanks
Cutter