Hello!
On 1st form, user selects day, month and colour (with comboboxes). On 2nd form, user writes cost, prize, entries, received and %. Then click on Save.
This data will go to an Excel worksheet template which has 3 tabs (red, green, blue).
What I need is a code so when I select on 1st form "2016", "may" and"colour blue" --> data will save in the 3rd tab (blue) and in the respective cell.
thanks very much!
here is an example
forum.ozgrid.com/index.php?attachment/68735/
Re: data addition to excel filtered by comboboxes
I changed around your code and form for testing purposes, however, this should point you in the direction your looking for.
I attached your sheet back so you can see the changes I made to UserForm1 -> What I did in Frame1 you should be able to implement in UserForm2
Good Luck..
[ATTACH=CONFIG]68736[/ATTACH]
Private Sub UserForm_Initialize()
cboMonth.AddItem "Month"
cboMonth.AddItem "January"
cboMonth.AddItem "February"
cboMonth.AddItem "March"
cboMonth.AddItem "April"
cboMonth.AddItem "May"
cboMonth.AddItem "June"
cboMonth.AddItem "July"
cboMonth.AddItem "August"
cboMonth.AddItem "September"
cboMonth.AddItem "October"
cboMonth.AddItem "November"
cboMonth.AddItem "December"
cboMonth.ListIndex = 0
cboYear.AddItem "Year"
cboYear.AddItem "2016"
cboYear.AddItem "2017"
cboYear.ListIndex = 0
cboColour.AddItem "Colour"
cboColour.AddItem "Green"
cboColour.AddItem "Red"
cboColour.AddItem "Blue"
cboColour.ListIndex = 0
Frame1.Visible = False
Me.Height = 93.75
End Sub
Private Sub UserForm_Activate()
With Me
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
End With
End Sub
Private Sub cboColour_Change()
If cboMonth.ListIndex > 0 And cboYear.ListIndex > 0 And cboColour.ListIndex > 0 Then
Frame1.Visible = True
Me.Height = 248.25
Else
Frame1.Visible = False
Me.Height = 93.75
End If
End Sub
Private Sub cboMonth_Change()
If cboMonth.ListIndex > 0 And cboYear.ListIndex > 0 And cboColour.ListIndex > 0 Then
Frame1.Visible = True
Me.Height = 248.25
Else
Frame1.Visible = False
Me.Height = 93.75
End If
End Sub
Private Sub cboYear_Change()
If cboMonth.ListIndex > 0 And cboYear.ListIndex > 0 And cboColour.ListIndex > 0 Then
Frame1.Visible = True
Me.Height = 248.25
Else
Frame1.Visible = False
Me.Height = 93.75
End If
End Sub
Private Sub cmdSave_Click()
Dim ws As Worksheet
Dim lCol As Long
Set ws = Sheets(cboColour.Value)
With ws
For lCol = 2 To .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If CStr(.Cells(2, lCol)) = cboYear.Value And .Cells(3, lCol) = cboMonth.Value Then
.Cells(4, lCol) = TextBox1.Value
.Cells(6, lCol) = TextBox2.Value
.Cells(7, lCol) = TextBox3.Value
.Cells(9, lCol) = TextBox5.Value
.Cells(11, lCol) = TextBox6.Value
GoTo Jump
End If
Next lCol
End With
MsgBox "Month and/or Year not Found"
Jump:
TextBox1.Value = vbNullString
TextBox2.Value = vbNullString
TextBox3.Value = vbNullString
TextBox5.Value = vbNullString
TextBox6.Value = vbNullString
Me.Frame1.Visible = False
Me.Height = 93.75
cboMonth.ListIndex = 0
cboYear.ListIndex = 0
cboColour.ListIndex = 0
End SubRe: data addition to excel filtered by comboboxes
Fuzz-Head, that's exactly what I was looking for!! thanks for your time!
Even your design gives me more things to learn and apply.
Thank you SO much!
*EDIT:
if you don't mind, could you explain me what does these lines do?
For lCol = 2 To .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
GoTo Jump
Jump:
TextBox1.Value = vbNullStringwhich is the difference between vbNullString and "" or 0?
thanks again sir!
Re: data addition to excel filtered by comboboxes
Quote from ignasis;768392Display MoreFuzz-Head, that's exactly what I was looking for!! thanks for your time!
Even your design gives me more things to learn and apply.
Thank you SO much!
*EDIT:
if you don't mind, could you explain me what does these lines do?
CodeFor lCol = 2 To .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column GoTo Jump Jump: TextBox1.Value = vbNullString
which is the difference between vbNullString and "" or 0?
thanks again sir!
Based on your example, I saw any date data starts on the second column so that is why you see 2
the .cells.find statement allows me to search the far most right column within your sheet
goto jump is what I sometimes do when I want to jump over looking at something, so in this case, if the wroksheet was the not a sheet I want to view, then I jumped over it.
vbnullstring simply erases the data someone inputed leaving it blank for the next time it is to be used.
Hope this helps. Glad it worked for you..
Re: data addition to excel filtered by comboboxes
sure it helped, I did understand everything!
thank you very much sir!
Don’t have an account yet? Register yourself now and be a part of our community!