Populate sheet from userform

cityfc · 04-14-2006, 06:35 PM

I have problems

1, i have combo box 1 on a userform and what i want to do is list the names
from worksheet "input sheet" and are in column g5:g20

2 then in combo box 2 list f1, f2, ot1, int, f3, f4, summary, o2 then in a
text box user inserts date (and only a date) then presses ok

now the tricky bit

when ok is pressed if name "a" and "f1" is selected in combo boxes the date
is inserted to worksheet "DM1" and cell d4
if name "a" and "f2" is selected in combo boxes the date is inserted to
worksheet "DM1" and cell e4
if name "b" and "f2" is selected in combo boxes the date is inserted to
worksheet "DM1" and cell e6

and so on

please help this has been tormenting me for weeks or other ideas that could
be simpler if mine is to complex

thanks in advance

Toppers · 04-15-2006, 07:25 AM

Try this - I wasn't sure about the rows but assumed they increased by 2 for
each name:

Private Sub CommandButton1_Click()
Dim j as long, i as integer
i = ComboBox1.ListIndex
j = ComboBox2.ListIndex
Worksheets("DM1").Cells((j * 2) + 4, i + 4) = _
Format (TextBox1.Value, "dd/mm/yyyy")
End Sub

Private Sub UserForm_Initialize()
Dim i as integer
For i = 5 To 20
ComboBox1.AddItem Worksheets("Input Sheet").Cells(i, "G")
Next i
cb2 = Array("f1", "f2", "ot1", "int", "f3", "f4", "summary", "o2")
For i = 0 To UBound(cb2)
ComboBox2.AddItem cb2(i)
Next i
End Sub

HTH

"cityfc" wrote:

> I have problems
>
> 1, i have combo box 1 on a userform and what i want to do is list the names
> from worksheet "input sheet" and are in column g5:g20
>
> 2 then in combo box 2 list f1, f2, ot1, int, f3, f4, summary, o2 then in a
> text box user inserts date (and only a date) then presses ok
>
> now the tricky bit
>
> when ok is pressed if name "a" and "f1" is selected in combo boxes the date
> is inserted to worksheet "DM1" and cell d4
> if name "a" and "f2" is selected in combo boxes the date is inserted to
> worksheet "DM1" and cell e4
> if name "b" and "f2" is selected in combo boxes the date is inserted to
> worksheet "DM1" and cell e6
>
> and so on
>
> please help this has been tormenting me for weeks or other ideas that could
> be simpler if mine is to complex
>
> thanks in advance

cityfc · 04-15-2006, 10:10 AM

Thank you for the quick response

it looks something like what im after i just tweaked it a little as follows

---------------------------------------
Private Sub CommandButton1_Click()
Dim j As Long, i As Integer
i = ComboBox1.ListIndex
j = ComboBox2.ListIndex
Worksheets("DM1").Cells((j * 2) + 5, i + 4) = _
Format(TextBox1.Value, "dd/mmm/yy")
End Sub

Private Sub UserForm_Initialize()
Dim i As Integer
For i = 4 To 62
ComboBox1.AddItem Worksheets("dm1").Cells(i, "a")
Next i
cb2 = Array("FDA 1", "OTDR 1", "FDA 2", "INTERIM", "FDA 3", "OTDR 2", "FDA
4", "SUMMARY")
For i = 0 To UBound(cb2)
ComboBox2.AddItem cb2(i)
Next i
End Sub

----------------------------------------------
the names are working fine, fda 1 etc are ok to

now i choose first name and fda 1 and input date then press ok it goes in to
the correct cell (d5) great. now if i choose otdr 1 instead of fda 1 it
places the date 2 cells down (d7) instead of 2 cells to right (f5)

hope this makes sense

"Toppers" wrote:

> Try this - I wasn't sure about the rows but assumed they increased by 2 for
> each name:
>
> Private Sub CommandButton1_Click()
> Dim j as long, i as integer
> i = ComboBox1.ListIndex
> j = ComboBox2.ListIndex
> Worksheets("DM1").Cells((j * 2) + 4, i + 4) = _
> Format (TextBox1.Value, "dd/mm/yyyy")
> End Sub
>
>
> Private Sub UserForm_Initialize()
> Dim i as integer
> For i = 5 To 20
> ComboBox1.AddItem Worksheets("Input Sheet").Cells(i, "G")
> Next i
> cb2 = Array("f1", "f2", "ot1", "int", "f3", "f4", "summary", "o2")
> For i = 0 To UBound(cb2)
> ComboBox2.AddItem cb2(i)
> Next i
> End Sub
>
> HTH
>
> "cityfc" wrote:
>
> > I have problems
> >
> > 1, i have combo box 1 on a userform and what i want to do is list the names
> > from worksheet "input sheet" and are in column g5:g20
> >
> > 2 then in combo box 2 list f1, f2, ot1, int, f3, f4, summary, o2 then in a
> > text box user inserts date (and only a date) then presses ok
> >
> > now the tricky bit
> >
> > when ok is pressed if name "a" and "f1" is selected in combo boxes the date
> > is inserted to worksheet "DM1" and cell d4
> > if name "a" and "f2" is selected in combo boxes the date is inserted to
> > worksheet "DM1" and cell e4
> > if name "b" and "f2" is selected in combo boxes the date is inserted to
> > worksheet "DM1" and cell e6
> >
> > and so on
> >
> > please help this has been tormenting me for weeks or other ideas that could
> > be simpler if mine is to complex
> >
> > thanks in advance

Toppers · 04-15-2006, 11:40 AM

Try:

Worksheets("DM1").Cells(i + 5, j * 2 + 4) = _
Format(TextBox1.Value, "dd/mmm/yy")

"cityfc" wrote:

> Thank you for the quick response
>
> it looks something like what im after i just tweaked it a little as follows
>
> ---------------------------------------
> Private Sub CommandButton1_Click()
> Dim j As Long, i As Integer
> i = ComboBox1.ListIndex
> j = ComboBox2.ListIndex
> Worksheets("DM1").Cells((j * 2) + 5, i + 4) = _
> Format(TextBox1.Value, "dd/mmm/yy")
> End Sub
>
>
> Private Sub UserForm_Initialize()
> Dim i As Integer
> For i = 4 To 62
> ComboBox1.AddItem Worksheets("dm1").Cells(i, "a")
> Next i
> cb2 = Array("FDA 1", "OTDR 1", "FDA 2", "INTERIM", "FDA 3", "OTDR 2", "FDA
> 4", "SUMMARY")
> For i = 0 To UBound(cb2)
> ComboBox2.AddItem cb2(i)
> Next i
> End Sub
>
> ----------------------------------------------
> the names are working fine, fda 1 etc are ok to
>
> now i choose first name and fda 1 and input date then press ok it goes in to
> the correct cell (d5) great. now if i choose otdr 1 instead of fda 1 it
> places the date 2 cells down (d7) instead of 2 cells to right (f5)
>
> hope this makes sense
>
>
>
>
> "Toppers" wrote:
>
> > Try this - I wasn't sure about the rows but assumed they increased by 2 for
> > each name:
> >
> > Private Sub CommandButton1_Click()
> > Dim j as long, i as integer
> > i = ComboBox1.ListIndex
> > j = ComboBox2.ListIndex
> > Worksheets("DM1").Cells((j * 2) + 4, i + 4) = _
> > Format (TextBox1.Value, "dd/mm/yyyy")
> > End Sub
> >
> >
> > Private Sub UserForm_Initialize()
> > Dim i as integer
> > For i = 5 To 20
> > ComboBox1.AddItem Worksheets("Input Sheet").Cells(i, "G")
> > Next i
> > cb2 = Array("f1", "f2", "ot1", "int", "f3", "f4", "summary", "o2")
> > For i = 0 To UBound(cb2)
> > ComboBox2.AddItem cb2(i)
> > Next i
> > End Sub
> >
> > HTH
> >
> > "cityfc" wrote:
> >
> > > I have problems
> > >
> > > 1, i have combo box 1 on a userform and what i want to do is list the names
> > > from worksheet "input sheet" and are in column g5:g20
> > >
> > > 2 then in combo box 2 list f1, f2, ot1, int, f3, f4, summary, o2 then in a
> > > text box user inserts date (and only a date) then presses ok
> > >
> > > now the tricky bit
> > >
> > > when ok is pressed if name "a" and "f1" is selected in combo boxes the date
> > > is inserted to worksheet "DM1" and cell d4
> > > if name "a" and "f2" is selected in combo boxes the date is inserted to
> > > worksheet "DM1" and cell e4
> > > if name "b" and "f2" is selected in combo boxes the date is inserted to
> > > worksheet "DM1" and cell e6
> > >
> > > and so on
> > >
> > > please help this has been tormenting me for weeks or other ideas that could
> > > be simpler if mine is to complex
> > >
> > > thanks in advance

cityfc · 04-15-2006, 01:45 PM

Excellent that works perfect i cant thank you enough, it has been driving me
mad

could i ask 2 more little favours

in the text box for the date is it possible that numbers only can be entered
so a date comes up for e.g. __/__/__ would show so people only enter numbers
like 090106 and no forward slashes and then the format would show in
worksheet dm1 cell whatever 09-JAN-06 then ask if they want to insert another
date

and also, sorry, when they press to insert date can the macro look to see if
cell is already populated, if it is it puts up a screen "assessment already
completed"

thanks so much again

"Toppers" wrote:

> Try:
>
> Worksheets("DM1").Cells(i + 5, j * 2 + 4) = _
> Format(TextBox1.Value, "dd/mmm/yy")
>
> "cityfc" wrote:
>
> > Thank you for the quick response
> >
> > it looks something like what im after i just tweaked it a little as follows
> >
> > ---------------------------------------
> > Private Sub CommandButton1_Click()
> > Dim j As Long, i As Integer
> > i = ComboBox1.ListIndex
> > j = ComboBox2.ListIndex
> > Worksheets("DM1").Cells((j * 2) + 5, i + 4) = _
> > Format(TextBox1.Value, "dd/mmm/yy")
> > End Sub
> >
> >
> > Private Sub UserForm_Initialize()
> > Dim i As Integer
> > For i = 4 To 62
> > ComboBox1.AddItem Worksheets("dm1").Cells(i, "a")
> > Next i
> > cb2 = Array("FDA 1", "OTDR 1", "FDA 2", "INTERIM", "FDA 3", "OTDR 2", "FDA
> > 4", "SUMMARY")
> > For i = 0 To UBound(cb2)
> > ComboBox2.AddItem cb2(i)
> > Next i
> > End Sub
> >
> > ----------------------------------------------
> > the names are working fine, fda 1 etc are ok to
> >
> > now i choose first name and fda 1 and input date then press ok it goes in to
> > the correct cell (d5) great. now if i choose otdr 1 instead of fda 1 it
> > places the date 2 cells down (d7) instead of 2 cells to right (f5)
> >
> > hope this makes sense
> >
> >
> >
> >
> > "Toppers" wrote:
> >
> > > Try this - I wasn't sure about the rows but assumed they increased by 2 for
> > > each name:
> > >
> > > Private Sub CommandButton1_Click()
> > > Dim j as long, i as integer
> > > i = ComboBox1.ListIndex
> > > j = ComboBox2.ListIndex
> > > Worksheets("DM1").Cells((j * 2) + 4, i + 4) = _
> > > Format (TextBox1.Value, "dd/mm/yyyy")
> > > End Sub
> > >
> > >
> > > Private Sub UserForm_Initialize()
> > > Dim i as integer
> > > For i = 5 To 20
> > > ComboBox1.AddItem Worksheets("Input Sheet").Cells(i, "G")
> > > Next i
> > > cb2 = Array("f1", "f2", "ot1", "int", "f3", "f4", "summary", "o2")
> > > For i = 0 To UBound(cb2)
> > > ComboBox2.AddItem cb2(i)
> > > Next i
> > > End Sub
> > >
> > > HTH
> > >
> > > "cityfc" wrote:
> > >
> > > > I have problems
> > > >
> > > > 1, i have combo box 1 on a userform and what i want to do is list the names
> > > > from worksheet "input sheet" and are in column g5:g20
> > > >
> > > > 2 then in combo box 2 list f1, f2, ot1, int, f3, f4, summary, o2 then in a
> > > > text box user inserts date (and only a date) then presses ok
> > > >
> > > > now the tricky bit
> > > >
> > > > when ok is pressed if name "a" and "f1" is selected in combo boxes the date
> > > > is inserted to worksheet "DM1" and cell d4
> > > > if name "a" and "f2" is selected in combo boxes the date is inserted to
> > > > worksheet "DM1" and cell e4
> > > > if name "b" and "f2" is selected in combo boxes the date is inserted to
> > > > worksheet "DM1" and cell e6
> > > >
> > > > and so on
> > > >
> > > > please help this has been tormenting me for weeks or other ideas that could
> > > > be simpler if mine is to complex
> > > >
> > > > thanks in advance

Toppers · 04-15-2006, 03:30 PM

I have modified the CommandButton1_Click routine and added another routine
(textbox1_Exit) which checks if a date has been entered. [Currently there is
no check to see if the date entry is valid; You will have add you own code to
do this as Excel. doesn't provide any function to do this!]

HTH

Private Sub CommandButton1_Click()
Dim j As Long, i As Integer
i = ComboBox1.ListIndex
j = ComboBox2.ListIndex
If Len(TextBox1.Value) <> 6 Or Len(TextBox1.Value) = 0 Then
MsgBox "Please enter date as ddmmyy e.g. 160406"
TextBox1.Value = ""
TextBox1.SetFocus
Exit Sub
End If
If Worksheets("DM1").Cells(i + 5, j * 2 + 4) <> "" Then
MsgBox "Assessment already completed"
Else
Worksheets("DM1").Cells(i + 5, j * 2 + 4) = _
CDate(Left(TextBox1.Value, 2) & "/" & Mid(TextBox1.Value, 3, 2) & "/" &
Right(TextBox1.Value, 2))
End If
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(TextBox1.Value) <> 6 Or Len(TextBox1.Value) = 0 Then
MsgBox "Please enter date as ddmmyy e.g. 160406"
TextBox1.Value = ""
End If
TextBox1.SetFocus
End Sub

Private Sub UserForm_Initialize()
Dim i As Integer
For i = 4 To 62
ComboBox1.AddItem Worksheets("dm1").Cells(i, "a")
Next i
cb2 = Array("FDA 1", "OTDR 1", "FDA 2", "INTERIM", "FDA 3", "OTDR 2",
"FDA4", "SUMMARY")
For i = 0 To UBound(cb2)
ComboBox2.AddItem cb2(i)
Next i
End Sub

"cityfc" wrote:

> Excellent that works perfect i cant thank you enough, it has been driving me
> mad
>
> could i ask 2 more little favours
>
> in the text box for the date is it possible that numbers only can be entered
> so a date comes up for e.g. __/__/__ would show so people only enter numbers
> like 090106 and no forward slashes and then the format would show in
> worksheet dm1 cell whatever 09-JAN-06 then ask if they want to insert another
> date
>
> and also, sorry, when they press to insert date can the macro look to see if
> cell is already populated, if it is it puts up a screen "assessment already
> completed"
>
> thanks so much again
>
>
> "Toppers" wrote:
>
> > Try:
> >
> > Worksheets("DM1").Cells(i + 5, j * 2 + 4) = _
> > Format(TextBox1.Value, "dd/mmm/yy")
> >
> > "cityfc" wrote:
> >
> > > Thank you for the quick response
> > >
> > > it looks something like what im after i just tweaked it a little as follows
> > >
> > > ---------------------------------------
> > > Private Sub CommandButton1_Click()
> > > Dim j As Long, i As Integer
> > > i = ComboBox1.ListIndex
> > > j = ComboBox2.ListIndex
> > > Worksheets("DM1").Cells((j * 2) + 5, i + 4) = _
> > > Format(TextBox1.Value, "dd/mmm/yy")
> > > End Sub
> > >
> > >
> > > Private Sub UserForm_Initialize()
> > > Dim i As Integer
> > > For i = 4 To 62
> > > ComboBox1.AddItem Worksheets("dm1").Cells(i, "a")
> > > Next i
> > > cb2 = Array("FDA 1", "OTDR 1", "FDA 2", "INTERIM", "FDA 3", "OTDR 2", "FDA
> > > 4", "SUMMARY")
> > > For i = 0 To UBound(cb2)
> > > ComboBox2.AddItem cb2(i)
> > > Next i
> > > End Sub
> > >
> > > ----------------------------------------------
> > > the names are working fine, fda 1 etc are ok to
> > >
> > > now i choose first name and fda 1 and input date then press ok it goes in to
> > > the correct cell (d5) great. now if i choose otdr 1 instead of fda 1 it
> > > places the date 2 cells down (d7) instead of 2 cells to right (f5)
> > >
> > > hope this makes sense
> > >
> > >
> > >
> > >
> > > "Toppers" wrote:
> > >
> > > > Try this - I wasn't sure about the rows but assumed they increased by 2 for
> > > > each name:
> > > >
> > > > Private Sub CommandButton1_Click()
> > > > Dim j as long, i as integer
> > > > i = ComboBox1.ListIndex
> > > > j = ComboBox2.ListIndex
> > > > Worksheets("DM1").Cells((j * 2) + 4, i + 4) = _
> > > > Format (TextBox1.Value, "dd/mm/yyyy")
> > > > End Sub
> > > >
> > > >
> > > > Private Sub UserForm_Initialize()
> > > > Dim i as integer
> > > > For i = 5 To 20
> > > > ComboBox1.AddItem Worksheets("Input Sheet").Cells(i, "G")
> > > > Next i
> > > > cb2 = Array("f1", "f2", "ot1", "int", "f3", "f4", "summary", "o2")
> > > > For i = 0 To UBound(cb2)
> > > > ComboBox2.AddItem cb2(i)
> > > > Next i
> > > > End Sub
> > > >
> > > > HTH
> > > >
> > > > "cityfc" wrote:
> > > >
> > > > > I have problems
> > > > >
> > > > > 1, i have combo box 1 on a userform and what i want to do is list the names
> > > > > from worksheet "input sheet" and are in column g5:g20
> > > > >
> > > > > 2 then in combo box 2 list f1, f2, ot1, int, f3, f4, summary, o2 then in a
> > > > > text box user inserts date (and only a date) then presses ok
> > > > >
> > > > > now the tricky bit
> > > > >
> > > > > when ok is pressed if name "a" and "f1" is selected in combo boxes the date
> > > > > is inserted to worksheet "DM1" and cell d4
> > > > > if name "a" and "f2" is selected in combo boxes the date is inserted to
> > > > > worksheet "DM1" and cell e4
> > > > > if name "b" and "f2" is selected in combo boxes the date is inserted to
> > > > > worksheet "DM1" and cell e6
> > > > >
> > > > > and so on
> > > > >
> > > > > please help this has been tormenting me for weeks or other ideas that could
> > > > > be simpler if mine is to complex
> > > > >
> > > > > thanks in advance