Banksy93
11-01-2017, 09:40 AM
Afternoon all,
im very new to the game of coding and wanted to see if i can get some help, personally im struggling to find material to help me learn
i understand a fair amount on formulas and etc.
I'm trying to use a userform to capture data that is entered by the user, (would be used by hundreds of people if released)
i have the basics down, a userform that captures the data using some code i managed to find on a website, and the data does get captured.
however im now trying to get a formula to be used to make the user have to input less and make it less likely to have mistakes
20835
(this is the user form i have created, ignore the multipage)
im wanting to remove managers name completely and automate that on the spreadsheet
Private Sub Self_Serve_SubButton_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'''find first empty row in database
''iRow = ws.Cells(Rows.Count, 1) _
'' .End(xlUp).Offset(1, 0).Row
'revised code to avoid problems with Excel tables in newer versions
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a part number
If Trim(Me.txt_ref.Value) = "" Then
Me.txt_ref.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txt_ref.Value
ws.Cells(iRow, 2).Value = Me.DD_box1.Value
ws.Cells(iRow, 3).Value = Me.DD_box2.Value
ws.Cells(iRow, 4).Value = Me.DD_box3.Value
ws.Cells(iRow, 5).Value = Me.DD_box4.Value
'clear the data
Me.txt_ref.Value = ""
Me.DD_box1.Value = ""
Me.DD_box2.Value = ""
Me.DD_box3.Value = ""
Me.DD_box4.Value = ""
Me.txt_ref.SetFocus
End Sub
20836
(my current spreadsheet, where the information is collected)
on the part marked 1 has a problem where if there is anything populated to the right, outside the normal range for the data it skips over them to a fresh Row.
i understand its in the code somewhere but for the life of me i cant work it out.
and also is this the only way to present this code? i was trying to get it to pick a whole column up instead of individual cells?
=IF(OR(C2=$J$10,C2=$J$11),"Dan",IF(OR(C2=$L$10,C2=$L$11),"Viv",IF(OR(C2=$N$10,C2=$N$11),"Sarah",IF(C2="","No Record","Unrecgonised Name!"))))
hope someone can help
Thanks in advance
Craig, the resident newbie
im very new to the game of coding and wanted to see if i can get some help, personally im struggling to find material to help me learn
i understand a fair amount on formulas and etc.
I'm trying to use a userform to capture data that is entered by the user, (would be used by hundreds of people if released)
i have the basics down, a userform that captures the data using some code i managed to find on a website, and the data does get captured.
however im now trying to get a formula to be used to make the user have to input less and make it less likely to have mistakes
20835
(this is the user form i have created, ignore the multipage)
im wanting to remove managers name completely and automate that on the spreadsheet
Private Sub Self_Serve_SubButton_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'''find first empty row in database
''iRow = ws.Cells(Rows.Count, 1) _
'' .End(xlUp).Offset(1, 0).Row
'revised code to avoid problems with Excel tables in newer versions
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a part number
If Trim(Me.txt_ref.Value) = "" Then
Me.txt_ref.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txt_ref.Value
ws.Cells(iRow, 2).Value = Me.DD_box1.Value
ws.Cells(iRow, 3).Value = Me.DD_box2.Value
ws.Cells(iRow, 4).Value = Me.DD_box3.Value
ws.Cells(iRow, 5).Value = Me.DD_box4.Value
'clear the data
Me.txt_ref.Value = ""
Me.DD_box1.Value = ""
Me.DD_box2.Value = ""
Me.DD_box3.Value = ""
Me.DD_box4.Value = ""
Me.txt_ref.SetFocus
End Sub
20836
(my current spreadsheet, where the information is collected)
on the part marked 1 has a problem where if there is anything populated to the right, outside the normal range for the data it skips over them to a fresh Row.
i understand its in the code somewhere but for the life of me i cant work it out.
and also is this the only way to present this code? i was trying to get it to pick a whole column up instead of individual cells?
=IF(OR(C2=$J$10,C2=$J$11),"Dan",IF(OR(C2=$L$10,C2=$L$11),"Viv",IF(OR(C2=$N$10,C2=$N$11),"Sarah",IF(C2="","No Record","Unrecgonised Name!"))))
hope someone can help
Thanks in advance
Craig, the resident newbie