• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to enter all the User Form data at one click

I am working on a project with a complex situation.I have
some Text boxes,Labels,Check boxes and a save button.i want
to fill my sheet in such a way that when i click on save
button. I require

  • All the text box data with only check box true value to be enter into sheet.
  • The Job Description should be enter only with first Entry. (as shown in the screen shot)
  • Head of Acc should be the caption of the label.(As Show in the Screen Shot)
  • The date and V.No (Auto Generated) should be enter at the same time with every entry with one click.
I request to any experience person to guide me.
Screen shot and file are attached. Thanks

70943
 

Attachments

Hui

Excel Ninja
Staff member
Go into VBA and add this code to the SAVE Button
ie: Double click the Save button and add this code

Code:
Private Sub CommandButton1_Click()
   
Dim lr As Integer

If CheckBox1 Then
  lr = Range("H" & Rows.Count).End(xlUp).Row
  Cells(lr + 1, 8).Value = TextBox2.Value
  Cells(lr + 1, 9).Value = TextBox6.Value
  Cells(lr + 1, 10).Value = "Fuel expenses"
  Cells(lr + 1, 11).Value = TextBox1.Value
  Cells(lr + 1, 12).Value = TextBox3.Value
End If
   
If CheckBox2 Then
  lr = Range("H" & Rows.Count).End(xlUp).Row
  Cells(lr + 1, 8).Value = TextBox2.Value
  Cells(lr + 1, 9).Value = TextBox6.Value
  Cells(lr + 1, 10).Value = "Travelling expenses"
  Cells(lr + 1, 11).Value = TextBox1.Value
  Cells(lr + 1, 12).Value = TextBox4.Value
End If
   
If CheckBox3 Then
  lr = Range("H" & Rows.Count).End(xlUp).Row
  Cells(lr + 1, 8).Value = TextBox2.Value
  Cells(lr + 1, 9).Value = TextBox6.Value
  Cells(lr + 1, 10).Value = "Mobile expenses"
  Cells(lr + 1, 11).Value = TextBox1.Value
  Cells(lr + 1, 12).Value = TextBox4.Value
End If
   
If CheckBox4 Then
  lr = Range("H" & Rows.Count).End(xlUp).Row
  Cells(lr + 1, 8).Value = TextBox2.Value
  Cells(lr + 1, 9).Value = TextBox6.Value
  Cells(lr + 1, 10).Value = "Tool expenses"
  Cells(lr + 1, 11).Value = TextBox1.Value
  Cells(lr + 1, 12).Value = TextBox7.Value
End If
    
Me.Hide
   
End Sub
 
Go into VBA and add this code to the SAVE Button
ie: Double click the Save button and add this code

Code:
Private Sub CommandButton1_Click()
  
Dim lr As Integer

If CheckBox1 Then
  lr = Range("H" & Rows.Count).End(xlUp).Row
  Cells(lr + 1, 8).Value = TextBox2.Value
  Cells(lr + 1, 9).Value = TextBox6.Value
  Cells(lr + 1, 10).Value = "Fuel expenses"
  Cells(lr + 1, 11).Value = TextBox1.Value
  Cells(lr + 1, 12).Value = TextBox3.Value
End If
  
If CheckBox2 Then
  lr = Range("H" & Rows.Count).End(xlUp).Row
  Cells(lr + 1, 8).Value = TextBox2.Value
  Cells(lr + 1, 9).Value = TextBox6.Value
  Cells(lr + 1, 10).Value = "Travelling expenses"
  Cells(lr + 1, 11).Value = TextBox1.Value
  Cells(lr + 1, 12).Value = TextBox4.Value
End If
  
If CheckBox3 Then
  lr = Range("H" & Rows.Count).End(xlUp).Row
  Cells(lr + 1, 8).Value = TextBox2.Value
  Cells(lr + 1, 9).Value = TextBox6.Value
  Cells(lr + 1, 10).Value = "Mobile expenses"
  Cells(lr + 1, 11).Value = TextBox1.Value
  Cells(lr + 1, 12).Value = TextBox4.Value
End If
  
If CheckBox4 Then
  lr = Range("H" & Rows.Count).End(xlUp).Row
  Cells(lr + 1, 8).Value = TextBox2.Value
  Cells(lr + 1, 9).Value = TextBox6.Value
  Cells(lr + 1, 10).Value = "Tool expenses"
  Cells(lr + 1, 11).Value = TextBox1.Value
  Cells(lr + 1, 12).Value = TextBox7.Value
End If
   
Me.Hide
  
End Sub
Will you clear it that how the value of TextBox2 i.e the description is only enter with the first entry? and after that nothing should be enter as show in the screen shot.
 
Top