VanillaSky
09-09-2017, 08:06 AM
Hello.
I am attempting to create table as below:
https://i.gyazo.com/3edbf9938d1ae6af454ceff56f22a4d5.png
I've created userform to input new data in B:G range. However I can't make it work properly, as userform is looking for empty rows
to insert new entries when there are not empty cells in A and I columns. Please note the code below:
Sub CommandButton_Submit_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
myDate = Date
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a Name number
If Trim(Me.TextBox_Company_Name.Value) = "" Then
Me.TextBox_Company_Name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox_PI_Case.Value
ws.Cells(iRow, 2).Value = Me.TextBox_Company_Name.Value
ws.Cells(iRow, 3).Value = Me.TextBox_Status.Value
ws.Cells(iRow, 4).Value = Me.TextBox_RoR.Value
ws.Cells(iRow, 5).Value = Me.TextBox_Comments.Value
ws.Cells(iRow, 6).Value = myDate
MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.TextBox_PI_Case.Value = ""
Me.TextBox_Company_Name.Value = ""
Me.TextBox_Status.Value = ""
Me.TextBox_RoR.Value = ""
Me.TextBox_Comments.Value = ""
Me.TextBox_PI_Case.SetFocus
End Sub
I assume that I should specify Range for data output B:G columns, however my poor knowledge of VBA does not allow me to do that. :crying:
Is there any possibility to make it work or VBA only allows to insert data from userform starting with first column?
Thank you in advance.
I am attempting to create table as below:
https://i.gyazo.com/3edbf9938d1ae6af454ceff56f22a4d5.png
I've created userform to input new data in B:G range. However I can't make it work properly, as userform is looking for empty rows
to insert new entries when there are not empty cells in A and I columns. Please note the code below:
Sub CommandButton_Submit_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
myDate = Date
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a Name number
If Trim(Me.TextBox_Company_Name.Value) = "" Then
Me.TextBox_Company_Name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox_PI_Case.Value
ws.Cells(iRow, 2).Value = Me.TextBox_Company_Name.Value
ws.Cells(iRow, 3).Value = Me.TextBox_Status.Value
ws.Cells(iRow, 4).Value = Me.TextBox_RoR.Value
ws.Cells(iRow, 5).Value = Me.TextBox_Comments.Value
ws.Cells(iRow, 6).Value = myDate
MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.TextBox_PI_Case.Value = ""
Me.TextBox_Company_Name.Value = ""
Me.TextBox_Status.Value = ""
Me.TextBox_RoR.Value = ""
Me.TextBox_Comments.Value = ""
Me.TextBox_PI_Case.SetFocus
End Sub
I assume that I should specify Range for data output B:G columns, however my poor knowledge of VBA does not allow me to do that. :crying:
Is there any possibility to make it work or VBA only allows to insert data from userform starting with first column?
Thank you in advance.