Need Help on Excel Userform Coding

[RUPESH16]

Hi Friends,

I have attached One Excel file.

In the attached file i have created some Userforms, but i am unable to give command.
So we need help with coding.

Could you please help me.

Thanks in Advance.

Regards,
Rupesh

 

[Ashhu]

Dear Rupesh,
I am unable to complete the code as i am leaving now, but i have made sure the data to fill when you hit Next button. Gaps are still there, i mean Scope of Improvement are there.
Copy below code in Module, and try. same way you can do for rest. Sorry for imcomplete code, if no reply i will update it for you tomorrow.

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a part number
If Trim(Me.TextBox1.Value) = "" Then
  Me.TextBox1.SetFocus
  MsgBox "Please enter a part number"
  Exit Sub
End If

'copy the data to the database
'use protect and unprotect lines,
'    with your password
'    if worksheet is protected
With ws
'  .Unprotect Password:="password"
  .Cells(iRow, 1).Value = Me.TextBox1.Value
  .Cells(iRow, 2).Value = Me.TextBox2.Value
  .Cells(iRow, 3).Value = Me.TextBox3.Value
  .Cells(iRow, 4).Value = Me.TextBox4.Value
'  .Protect Password:="password"
End With

'clear the data

End Sub

 

[ANKUSHRS1]

try this......

 

[ANKUSHRS1]

Private Sub CommandButton1_Click()
Dim lastrow As Long

lastrow = Cells(Sheet1.Rows.Count, "b").End(xlUp).Row + 1

Range("a" & lastrow).Value = TextBox1.Value
Range("b" & lastrow).Value = TextBox2.Value
Range("c" & lastrow).Value = TextBox3.Value
Range("d" & lastrow).Value = TextBox4.Value
Range("e" & lastrow).Value = TextBox5.Value
Range("f" & lastrow).Value = TextBox6.Value
Range("g" & lastrow).Value = TextBox7.Value
Range("h" & lastrow).Value = TextBox8.Value

TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""

End Sub

 

[Deepak]

ANKUSHRS1

Pls use code tags while posting the same in body.

@RUPESH16

Check this..

Private Sub CommandButton1_Click()
Dim lastrow As Long, chk As Boolean, x As Object

With Sheet1
    lastrow = .Cells(.Rows.Count, "b").End(xlUp).Row + 1
   
        For i = 1 To 8
            If Not Me.Controls("TextBox" & i).Value <> "" Then
                Me.Controls("TextBox" & i).BackColor = vbRed: chk = True
            Else
                Me.Controls("TextBox" & i).BackColor = vbWhite
            End If
        Next
       
        If chk Then MsgBox "Pls correct rex boxes", vbCritical: Exit Sub
       
        For i = 1 To 8
            Set x = Me.Controls("TextBox" & i)
                .Cells(lastrow, i) = x.Value
              x.Value = "": x.BackColor = vbWhite
        Next
        Set x = Nothing
End With

End Sub

 

[RUPESH16]

Dear Rupesh,
I am unable to complete the code as i am leaving now, but i have made sure the data to fill when you hit Next button. Gaps are still there, i mean Scope of Improvement are there.
Copy below code in Module, and try. same way you can do for rest. Sorry for imcomplete code, if no reply i will update it for you tomorrow.

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a part number
If Trim(Me.TextBox1.Value) = "" Then
  Me.TextBox1.SetFocus
  MsgBox "Please enter a part number"
  Exit Sub
End If

'copy the data to the database
'use protect and unprotect lines,
'    with your password
'    if worksheet is protected
With ws
'  .Unprotect Password:="password"
  .Cells(iRow, 1).Value = Me.TextBox1.Value
  .Cells(iRow, 2).Value = Me.TextBox2.Value
  .Cells(iRow, 3).Value = Me.TextBox3.Value
  .Cells(iRow, 4).Value = Me.TextBox4.Value
'  .Protect Password:="password"
End With

'clear the data

End Sub

Hi Ashhu,
i
i am unable to complete the coding.
can you please help me,

Thank in advance

Regards,
Rupesh

 

[Ashhu]

Dear Rupesh,
Did you check ANKUSHRS1 solution? hope that will solve your need.

Ashwin