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