PDA

View Full Version : using excel to collect and store data



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

Banksy93
11-01-2017, 11:28 AM
Don't worry, after messing around with this a bit more and managing to find some material to go off, i've added to automation into the code and decided to deal with the sheet missing Rows with info in across the sheet.

if anyone has a better way to write this code then that would be a help. saves me writing huge lines.

for 1 manager and 10 agents:
If Me.DD_box2.Value = ws.Cells(3, 11).Value Or Me.DD_box2.Value = ws.Cells(4, 11).Value Or Me.DD_box2.Value = ws.Cells(5, 11).Value Or Me.DD_box2.Value = ws.Cells(6, 11).Value Or Me.DD_box2.Value = ws.Cells(7, 11).Value Or Me.DD_box2.Value = ws.Cells(8, 11).Value Or Me.DD_box2.Value = ws.Cells(9, 11).Value Or Me.DD_box2.Value = ws.Cells(10, 11).Value Or Me.DD_box2.Value = ws.Cells(11, 11).Value Or Me.DD_box2.Value = ws.Cells(12, 11).Value Then
ws.Cells(iRow, 4).Value = ws.Cells(2, 11).Value
End If

20837
for whatever reason i cant get ranges to work.

(if i ever get this complete and used, names for managers can be up to 20 with Agents being up to 30 for each manager)
now you can see why i would if there is an easier way to write the code.

SamT
11-01-2017, 06:24 PM
You need to structure the data so it helps the code. For Example, Managers and Agents.

That Table is worth it's own sheet. "Table Of Organi".... Whatever, and it's own CodeName."TOO" or whatever. This sheet will be hidden after testing, and you can make a UserFortm so SuperUsers can edit it without error.

In A1 and below, simply list all the Managers by Name or ID. This list is your Managers List/ComboBox's Column List

In C1, E1, and every other column, (Odd Numbered,) across the sheet, place a Managers Name or ID, With the Prefix "mgr_", (mgr_PointyHairedBoss) .

Immediately under each managers "mgr_" Title, place a list of his/her Agents.

The Code to access the Managers List on that Hidden Sheet is

ComboBoxManagers.List = TOO.Range("A1").CurrentRegion

A Manager's Agent list is

Dim Found As Range 'Used for Error Trapping

set Found = TOO.Find(ComboBoxManagers.Value).CurrentRegion
If Not Found Is Nothing then
ListBoxAgents.List = Found
End If

To remove the Top cell from the List

Set Found = Found.Offset(1).Resize(Found.Rows.Count - 1, Found.Columns.Count)
ListBoxAgents.List = Found

To get the Managers name from the lists of agents

Set Found = TOO.Find((AgentsName),CurrentRegion.Cells(1)
'Test Found
ManagersName = Found.Value


Range(NoRecord).Resize(-8, 1).Delete shift:=xlUp(?)Best to stop the "No Record" entry before it happens.

Banksy93
11-02-2017, 12:21 PM
Thanks SamT for taking your time to reply, much appreciated.

im failing at the first hurdle here.
I've made a new sheet named "TOO" for this purpose
laid it out as you have said and im still unable to get the combobox to take a range.
am i missing something complete obvious?

20849

I've tried adding the first line of code you have written in UserForm_initialize

sorry for being this newbie, I've only dabbled in C# a little bit and writing VB excel is fairly new to me.

thanks again

SamT
11-02-2017, 01:07 PM
Where's the code?

Banksy93
11-02-2017, 01:15 PM
sorry this is all my code:


Private Sub Self_Serve_ResButton_Click()


Me.txt_ref.Value = ""
Me.DD_box1.Value = ""
Me.DD_box2.Value = ""
Me.txt_ref.SetFocus


End Sub


Private Sub Self_Serve_SubButton_Click()




Dim iRow As Long
Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = Worksheets("Data")
Set ws2 = Worksheets("TOO")


'Row Finder
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'check for a Ref Number
If Trim(Me.txt_ref.Value) = "" Then
Me.txt_ref.SetFocus
MsgBox "Please enter a Reference number or N/A"
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


'Code for date and time
ws.Cells(iRow, 5).Value = Now


''Manager1 Coding
If Me.DD_box1.Value = ws.Cells(3, 10).Value Or Me.DD_box1.Value = ws.Cells(4, 10).Value Or Me.DD_box1.Value = ws.Cells(5, 10).Value Or Me.DD_box1.Value = ws.Cells(6, 10).Value Or Me.DD_box1.Value = ws.Cells(7, 10).Value Or Me.DD_box1.Value = ws.Cells(8, 10).Value Or Me.DD_box1.Value = ws.Cells(9, 10).Value Or Me.DD_box1.Value = ws.Cells(10, 10).Value Or Me.DD_box1.Value = ws.Cells(11, 10).Value Or Me.DD_box1.Value = ws.Cells(12, 10).Value Then
ws.Cells(iRow, 4).Value = ws.Cells(2, 10).Value
End If


''Manager2 Coding
If Me.DD_box1.Value = ws.Cells(3, 11).Value Or Me.DD_box1.Value = ws.Cells(4, 11).Value Or Me.DD_box1.Value = ws.Cells(5, 11).Value Or Me.DD_box1.Value = ws.Cells(6, 11).Value Or Me.DD_box1.Value = ws.Cells(7, 11).Value Or Me.DD_box1.Value = ws.Cells(8, 11).Value Or Me.DD_box1.Value = ws.Cells(9, 11).Value Or Me.DD_box1.Value = ws.Cells(10, 11).Value Or Me.DD_box1.Value = ws.Cells(11, 11).Value Or Me.DD_box1.Value = ws.Cells(12, 11).Value Then
ws.Cells(iRow, 4).Value = ws.Cells(2, 11).Value
End If


''Manager3 Coding
If Me.DD_box1.Value = ws.Cells(3, 12).Value Or Me.DD_box1.Value = ws.Cells(4, 12).Value Or Me.DD_box1.Value = ws.Cells(5, 12).Value Or Me.DD_box1.Value = ws.Cells(6, 1).Value Or Me.DD_box1.Value = ws.Cells(7, 12).Value Or Me.DD_box1.Value = ws.Cells(8, 12).Value Or Me.DD_box1.Value = ws.Cells(9, 12).Value Or Me.DD_box1.Value = ws.Cells(10, 12).Value Or Me.DD_box1.Value = ws.Cells(11, 12).Value Or Me.DD_box1.Value = ws.Cells(12, 12).Value Then
ws.Cells(iRow, 4).Value = ws.Cells(2, 12).Value
End If


'clear the data
Me.txt_ref.Value = ""
Me.DD_box1.Value = ""
Me.DD_box2.Value = ""
Me.txt_ref.SetFocus


End Sub




Private Sub UserForm_Initialize()


'Your_Name drop box info
DD_box1.AddItem "Agent1_1"
DD_box1.AddItem "Agent1_2"
DD_box1.AddItem "Agent1_3"
DD_box1.AddItem "Agent1_4"
DD_box1.AddItem "Agent1_5"
DD_box1.AddItem "Agent1_6"
DD_box1.AddItem "Agent1_7"
DD_box1.AddItem "Agent1_8"
DD_box1.AddItem "Agent1_9"
DD_box1.AddItem "Agent1_10"

DD_box1.AddItem "Agent2_1"
DD_box1.AddItem "Agent2_2"
DD_box1.AddItem "Agent2_3"
DD_box1.AddItem "Agent2_4"
DD_box1.AddItem "Agent2_5"
DD_box1.AddItem "Agent2_6"
DD_box1.AddItem "Agent2_7"
DD_box1.AddItem "Agent2_8"
DD_box1.AddItem "Agent2_9"
DD_box1.AddItem "Agent2_10"


DD_box1.AddItem "Agent3_1"
DD_box1.AddItem "Agent3_2"
DD_box1.AddItem "Agent3_3"
DD_box1.AddItem "Agent3_4"
DD_box1.AddItem "Agent3_5"
DD_box1.AddItem "Agent3_6"
DD_box1.AddItem "Agent3_7"
DD_box1.AddItem "Agent3_8"
DD_box1.AddItem "Agent3_9"
DD_box1.AddItem "Agent3_10"

'Un/Successful Drop Box Info
DD_box2.AddItem "Successful"
DD_box2.AddItem "UnSuccessful"

'''''' I was placing it here

End Sub

#I've attached the excel sheet if that makes it easier to see# 20850

snb
11-03-2017, 12:54 AM
See: http://www.snb-vba.eu/VBA_Userform_in_database_en.html