+ Reply to Thread
Results 1 to 10 of 10

Copy userform data to a second sheet based on ComboBox value?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-22-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    329

    Copy userform data to a second sheet based on ComboBox value?

    Hi all once again,

    Currently working on a code to be attached to a command button on a userform, I have the code to copy all fields to a specific sheet "AuditLog", however I also need the code to copy the same data to a second sheet based on the value in ComboBox1 eg. if ComboBox1 value is "CM1" then also copy data to sheet "CM1" in exactly the same way as originally done in "AuditLog" in next available row? Thanks to all again in advance & have a great day

    Code so far...

    Private Sub CommandButton1_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("AuditLog")
    
    Application.ScreenUpdating = False
    
    'find  first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    
    'check for a Input
    If Trim(Me.TextBox1.Value) = "" Then
    Me.TextBox1.SetFocus
    MsgBox "Please enter all fields"
    Exit Sub
    End If
    Sheets("AuditLog").Unprotect Password:="000"
    
    'copy the data to the database
    
    ws.Cells(iRow, "A").Value = Me.TextBox1.Value
    ws.Cells(iRow, "B").Value = Me.TextBox2.Value
    ws.Cells(iRow, "C").Value = Me.ComboBox1.Value
    ws.Cells(iRow, "D").Value = Me.TextBox3.Value
    ws.Cells(iRow, "E").Value = Me.TextBox4.Value
    ws.Cells(iRow, "F").Value = Me.TextBox5.Value
    ws.Cells(iRow, "G").Value = Me.ComboBox2.Value
    ws.Cells(iRow, "H").Value = Me.ComboBox3.Value
    
    'clear the data
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.ComboBox1.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.ComboBox2.Value = ""
    Me.ComboBox3.Value = ""
    Me.TextBox1.SetFocus
       
    End Sub

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Copy userform data to a second sheet based on ComboBox value?

    Hi

    try something like
    set ws = combobox1.value
    then rerun the same action code.

    you could put it in a loop (for i = 1 to 2) with an if statement
    if i = 1 then
      set ws = worksheets("auditlog"
    else
      set ws = worksheets(combobox1.value)
    end if
    rylo

  3. #3
    Forum Contributor
    Join Date
    04-22-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    329

    Re: Copy userform data to a second sheet based on ComboBox value?

    Hi rylo,

    I have four alternate pages, ranging from "CM1", "CM2", "CM3" & "CM4", can you give me an example of the code with one of these, I've tried the above code & copied the action code but I keep getting a "run-time error '9': Subscript out of range with "set ws = worksheets(combobox1.value)"?
    Last edited by matrixpom; 11-08-2012 at 12:27 AM.

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Copy userform data to a second sheet based on ComboBox value?

    Hi

    Can you put up the code that you tried.

    rylo

  5. #5
    Forum Contributor
    Join Date
    04-22-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    329

    Re: Copy userform data to a second sheet based on ComboBox value?

    Hi rylo,

    I've altered it a few times & managed to get it to run but it still doesn't send the data to Sheet "CM1"? Thanks for your time rylo - Marco

    Private Sub CommandButton1_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("AuditLog")
    
    Application.ScreenUpdating = False
    
    'find  first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    
    'check for a Input
    If Trim(Me.TextBox1.Value) = "" Then
    Me.TextBox1.SetFocus
    MsgBox "Please enter all fields"
    Exit Sub
    End If
    Sheets("AuditLog").Unprotect Password:="000"
    
    'copy the data to the database
    
    ws.Cells(iRow, "A").Value = Me.TextBox1.Value
    ws.Cells(iRow, "B").Value = Me.TextBox2.Value
    ws.Cells(iRow, "C").Value = Me.ComboBox1.Value
    ws.Cells(iRow, "D").Value = Me.TextBox3.Value
    ws.Cells(iRow, "E").Value = Me.TextBox4.Value
    ws.Cells(iRow, "F").Value = Me.TextBox5.Value
    ws.Cells(iRow, "G").Value = Me.ComboBox2.Value
    ws.Cells(iRow, "H").Value = Me.ComboBox3.Value
    
    End
    
      Set ws = Worksheets(ComboBox1.Value)
      
      'find  first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    
    'check for a Input
    If Trim(Me.ComboBox1.Value) = "" Then
    Me.ComboBox1.SetFocus
    MsgBox "Please enter all fields"
    Exit Sub
    
    End If
    
    ws = Worksheets(ComboBox1.Value).Unprotect
    
    'copy the data to the active sheet
    
    ws.Cells(iRow, "A").Value = Me.TextBox1.Value
    ws.Cells(iRow, "B").Value = Me.TextBox2.Value
    ws.Cells(iRow, "C").Value = Me.ComboBox1.Value
    ws.Cells(iRow, "D").Value = Me.TextBox3.Value
    ws.Cells(iRow, "E").Value = Me.TextBox4.Value
    ws.Cells(iRow, "F").Value = Me.TextBox5.Value
    ws.Cells(iRow, "G").Value = Me.ComboBox2.Value
    ws.Cells(iRow, "H").Value = Me.ComboBox3.Value
    
    Active.Sheets.Protect Password:="000"
    
    End
    
    
    'clear the data
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.ComboBox1.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.ComboBox2.Value = ""
    Me.ComboBox3.Value = ""
    Me.TextBox1.SetFocus
    
    Application.ScreenUpdating = True
    
    
    End Sub

  6. #6
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Copy userform data to a second sheet based on ComboBox value?

    Marco

    Seems a bit out of wack. First you set the variable ws to the combobox1.value - OK. Then you check to see if the combobox really has been selected and if it hasn't then do a messagebox, but you don't set the ws variable if no selection had been made. Then you completely undo the ws variable by giving it the result of the unprotect.

    If that doesn't make sense, then build an example workbook, with the 5 sheets, the form, the code and I'll have a look.

    rylo

  7. #7
    Forum Contributor
    Join Date
    04-22-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    329

    Re: Copy userform data to a second sheet based on ComboBox value?

    Hi rylo,

    ...my excel prowess is sadly way way below yours, any assistance you can give would be greatly appreciated, thanks again rylo! - Marco

    Test1.xls

  8. #8
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Copy userform data to a second sheet based on ComboBox value?

    Hi

    and with a loop to process the data

    Private Sub CommandButton1_Click()
      Dim iRow As Long, i As Long
      Dim ws As Worksheet
      Dim BlankOne As Boolean
    
    
      Application.ScreenUpdating = False
    
    
    'check for a Input
      BlankOne = False
      For i = 0 To Me.Controls.Count - 1
        Select Case TypeName(Me.Controls(i))
          Case "TextBox", "ComboBox"
            If Len(Trim(Me.Controls(i))) = 0 Then
              BlankOne = True
            End If
          Case Else
        End Select
      Next i
      'process a blank input item
      If BlankOne Then
        MsgBox "Please Enter All Fields"
        Exit Sub
      End If
      
      For i = 1 To 2
        If i = 1 Then
          Set ws = Worksheets("AuditLog")
        Else
          Set ws = Worksheets(Me.ComboBox1.Value)
        End If
      'find  first empty row in database
        iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    
        ws.Unprotect Password:="000"
    
    'copy the data to the database
      
        ws.Cells(iRow, "A").Value = Me.TextBox1.Value
        ws.Cells(iRow, "B").Value = Me.TextBox2.Value
        ws.Cells(iRow, "C").Value = Me.ComboBox1.Value
        ws.Cells(iRow, "D").Value = Me.TextBox3.Value
        ws.Cells(iRow, "E").Value = Me.TextBox4.Value
        ws.Cells(iRow, "F").Value = Me.TextBox5.Value
        ws.Cells(iRow, "G").Value = Me.ComboBox2.Value
        ws.Cells(iRow, "H").Value = Me.ComboBox3.Value
    
        ws.Protect Password:="000"
      
      Next i
    
    'clear the data
      Me.TextBox1.Value = ""
      Me.TextBox2.Value = ""
      Me.ComboBox1.Value = ""
      Me.TextBox3.Value = ""
      Me.TextBox4.Value = ""
      Me.TextBox5.Value = ""
      Me.ComboBox2.Value = ""
      Me.ComboBox3.Value = ""
      Me.TextBox1.SetFocus
    
      Application.ScreenUpdating = True
    
    
    End Sub
    rylo

  9. #9
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Copy userform data to a second sheet based on ComboBox value?

    Hi

    Nothing fancy here, so see how it goes.

    Private Sub CommandButton1_Click()
      Dim iRow As Long
      Dim ws As Worksheet
      Dim BlankOne As Boolean
      Set ws = Worksheets("AuditLog")
    
      Application.ScreenUpdating = False
    
    'find  first empty row in database
      iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    
    'check for a Input
      BlankOne = False
      For i = 0 To Me.Controls.Count - 1
        Select Case TypeName(Me.Controls(i))
          Case "TextBox", "ComboBox"
            If Len(Trim(Me.Controls(i))) = 0 Then
              BlankOne = True
            End If
          Case Else
        End Select
      Next i
      If BlankOne Then
        MsgBox "Please Enter All Fields"
        Exit Sub
      End If
      ws.Unprotect Password:="000"
    
    'copy the data to the database
      
      ws.Cells(iRow, "A").Value = Me.TextBox1.Value
      ws.Cells(iRow, "B").Value = Me.TextBox2.Value
      ws.Cells(iRow, "C").Value = Me.ComboBox1.Value
      ws.Cells(iRow, "D").Value = Me.TextBox3.Value
      ws.Cells(iRow, "E").Value = Me.TextBox4.Value
      ws.Cells(iRow, "F").Value = Me.TextBox5.Value
      ws.Cells(iRow, "G").Value = Me.ComboBox2.Value
      ws.Cells(iRow, "H").Value = Me.ComboBox3.Value
    
      ws.Protect Password:="000"
      
      
      Set ws = Worksheets(ComboBox1.Value)
      
      'find  first empty row in database
      iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    
    
      Worksheets(ComboBox1.Value).Unprotect Password:="000"
    
    'copy the data to the active sheet
    
      ws.Cells(iRow, "A").Value = Me.TextBox1.Value
      ws.Cells(iRow, "B").Value = Me.TextBox2.Value
      ws.Cells(iRow, "C").Value = Me.ComboBox1.Value
      ws.Cells(iRow, "D").Value = Me.TextBox3.Value
      ws.Cells(iRow, "E").Value = Me.TextBox4.Value
      ws.Cells(iRow, "F").Value = Me.TextBox5.Value
      ws.Cells(iRow, "G").Value = Me.ComboBox2.Value
      ws.Cells(iRow, "H").Value = Me.ComboBox3.Value
    
      ws.Protect Password:="000"
    
    
    
    
    'clear the data
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.ComboBox1.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.ComboBox2.Value = ""
    Me.ComboBox3.Value = ""
    Me.TextBox1.SetFocus
    
    Application.ScreenUpdating = True
    
    
    End Sub
    rylo

  10. #10
    Forum Contributor
    Join Date
    04-22-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    329

    Re: Copy userform data to a second sheet based on ComboBox value?

    Hi Rylo,

    Works like a charm! If you're ever down the Goldie let me know, first shouts on me ok, cheers rylo!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1