Code to copy from userform to logsheet not working, no error messages

Hi everyone.

Could someone please take a look at this code and tell me what I need to change to make it function. Background: Userform has info put into fields. After it is complete, user hit submit button which is tied to this:

Private Sub CMB_Submit_Click()
   Dim rw As Long    'next available row
 
   With Sheets("EEG Log")
 
      'get the next avialable row in Sheet1
      rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1
 
      'put the text box values in this row
      .Range("A" & rw).Value = TB_Date.Value
      .Range("B" & rw).Value = CMB_Employee.Value
      .Range("C" & rw).Value = Combobox1.Value
      .Range("D" & rw).Value = CMB_Absent.Value
      .Range("E" & rw).Value = TB_POINT.Value
      .Range("F" & rw).Value = TB_Description.Text
      .Range("G" & rw).Value = Combo_Charge.Text
      
           
   End With
   
   Dim resp As Integer
resp = MsgBox("Data submited to log." & vbCrLf & "Do you have another entry for this workgroup?", vbYesNo)
If resp = vbYes Then

With Sheets("EEG Log")
   
   
   CMB_Absent.Value = ""
   CMB_Employee.Value = ""
   Combobox1.Value = ""
   TB_Description.Text = ""
   TB_POINT.Value = ""
   Combo_Charge.Value = ""
   
  End With
  End If
  
  
If resp = vbNo Then
    CMB_Absent.Value = ""
   CMB_Employee.Value = ""
   Combobox1.Value = ""
   TB_Description.Text = ""
   TB_POINT.Value = ""
   Combo_Charge.Value = ""

ThisWorkbook.Save
   
    UF_EEGOCC.Hide
    UF_MANJP.Show
    
    
 
     End If
       
     End Sub

I don't see any errors on this, but it is not copying the data to the logsheet.

Any ideas?


Thanks for your help.

Patrick

I don't see a problem with the code, though I have a few suggestions for cleaning it up a bit (see below). Try putting a debug stop at the beginning and step through it. Make sure it's putting the data where it should and find out what's happening to it.

    End With

    CMB_Absent.Value = ""
    CMB_Employee.Value = ""
    Combobox1.Value = ""
    TB_Description.Text = ""
    TB_POINT.Value = ""
    Combo_Charge.Value = ""

    If MsgBox("Data submited to log." & vbCrLf & "Do you have another entry for this workgroup?", vbYesNo) = vbNo Then
        ThisWorkbook.Save
        UF_EEGOCC.Hide
        UF_MANJP.Show
    End If

Thanks for cleaning up the code for me Nate. I am still trying to figure out why it is not working. It seemed to quit around the same time I formatted the sheet as a table. Perhaps that has something to do with it.


Patrick

I am not quite sure how this happened, but my data was getting copied to the sheet, just starting at row 1000. I erased the data and tried again and it seems to copy fine. However, I have issues elsewhere. I am now getting a Type Mismatch error that is pointing back to another bit of code I have going. I am at a loss. When I fill out the userform and click the control attached to this:

Private Sub CMB_Submit_Click()
   Dim rw As Long    'next available row
 
   With Sheets("EEG_Log")
 
      'get the next avialable row in Sheet1
      rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1
 
      'put the text box values in this row
      .Range("A" & rw).Value = TB_Date.Value
      .Range("B" & rw).Value = CMB_Employee.Value
      .Range("C" & rw).Value = Combobox1.Value
      .Range("D" & rw).Value = TB_POINT.Value
      .Range("E" & rw).Value = TB_Description.Text
      .Range("F" & rw).Value = Combo_Charge.Text
      
           
   End With
   
   Dim resp As Integer
resp = MsgBox("Data submited to log." & vbCrLf & "Do you have another entry?", vbYesNo)
If resp = vbYes Then

With Sheets("EEG_Log")
   
   
   Combo_Charge.Text = ""
   CMB_Employee.Value = ""
   Combobox1.Value = ""
   TB_Description.Text = ""
   TB_POINT.Value = ""
    
  End With
  End If
  
  
If resp = vbNo Then

ThisWorkbook.Save
   
     UF_EEGOCC.Hide
    
    UF_MANJP.Show
    
    
 
     End If
     
   End Sub

It seems to complete and gets me to the message box. Clicking yes will error out Type Mismatch on this code:

Private Sub Combobox1_Change()

On Error Resume Next
    
    Dim Frm1 As String
        
   Frm1 = Application.VLookup(Me.Combobox1.Value, Sheets("Data_Sheet").Range("F2:G8"), 2, False)               
   
 TB_POINT = Frm1
        
    
End Sub

Any ideas on what I need to do differently?


Patrick

I have a named range "Infraction" on the Data_Sheet. I have is listed in the rowsource for my userform. The range does match the cells F2:G8.

Brilliant! Thank you so much Norie. Your code did the trick.

Patrick