Solved: Useforms Active Cell Selection

[sooty8]

Hi All

What code would be needed to ensure that if I select a name in ListBox1 on UserForm1, the ActiveCell selected would be in the correct row of Sheet1 of the workbook, Column("I") the Calendar is named as UserForm2. the code is below. There are over 500 rows on Sheet1 and all are in ListBox1 basically I suppose Sheet1 is a database using the RowSource.

Private Sub Calendar1_Click()
Application.ScreenUpdating = False
ActiveCell.Value = Calendar1()
With ActiveCell

.NumberFormat = "dd/mm/yy"
ActiveCell.Offset(0, 1) = ComboBox2.Value
ActiveCell.Offset(0, 2) = ComboBox3.Value
ActiveCell.Offset(0, 5) = ComboBox1.Value
ActiveCell.Offset(0, 4) = ComboBox4.Value
ActiveCell.Offset(0, 3) = ComboBox5.Value
ActiveCell.Select
End With
ComboBox2.Value = ""
ComboBox3.Value = ""
ComboBox1.Value = ""
ComboBox4.Value = ""
ComboBox5.Value = ""
Application.ScreenUpdating = True
End Sub

Any help much appreciated

Sooty8

[Bob Phillips]

[vba]

Columns("A:A").Cells(Application.Match(ListBox1.Value, Columns("A:A"), 0), 1).Select
[/vba]

[sooty8]

Hi - El Xid

First time I have posted and I return from lunch in the UK and my problem is solved many thanks for your help -- I just had to change the offsets slightly to get it all to work perfectly. My boss thinks I'm a genius - might even get a pay rise.

Sooty8

[Bob Phillips]

Sounds like the beers are on you <G>

[Bob Phillips]

PS your genius is in knowing where to look <ebg>

[sooty8]

Hi

I posted this yesterday and had tremendous help from xld with the following solution

Columns("A:A").Cells(Application.Match(ListBox1.Value, Columns("A:A"), 0), 1).Select

However this morning hit a snag if there are similar names and spellings in Row50 (Col "A") - MUNN (Col "B")STEVEN
Row51 (Col "A") - MUNN (Col "B")ROLAND

Enter data against Steven Munn all data enters the cells OK
Select Roland Munn ( in ListBox) enter data click Calendar1 and it just overwrites the data against Steven Munn. Seems difficult to explain my problem In writing ---- yesterday I was the genius today I'm an idiot



Private Sub Calendar1_Click()
Application.ScreenUpdating = False
ActiveCell.Value = Calendar1()
With ActiveCell

.NumberFormat = "dd/mm/yy"
ActiveCell.Offset(0, 1) = ComboBox2.Value
ActiveCell.Offset(0, 2) = ComboBox3.Value
ActiveCell.Offset(0, 5) = ComboBox1.Value
ActiveCell.Offset(0, 4) = ComboBox4.Value
ActiveCell.Offset(0, 3) = ComboBox5.Value
ActiveCell.Select
End With
ComboBox2.Value = ""
ComboBox3.Value = ""
ComboBox1.Value = ""
ComboBox4.Value = ""
ComboBox5.Value = ""
Application.ScreenUpdating = True
End Sub

Any help appreciated

Sooty8