Thread: Address Reference for selection from Range - VBA

Hi,

I am currently designing userform which allows user to add sickness or holidays in the sheet.These are copied one below the other and are working fine.
Absences once inputted need not be edited, however holidays may need to be changed. I have designed userform which basically combines Employee name and date as range and gives me data which has been entered in the sheet.

=OFFSET('DATABASE-H'!$B$1,1,0,COUNTA('DATABASE-H'!$B$2:$B$1500),1)
(REFERS TO THE RANGE)

This range is added to the row source property of combobox on the userform
There are 7 Textboxes which refere to corresponding holiday details for that Employee

Private Sub ComboBox1_Click()
Dim i%
ActiveCell.Select
'[a1].Select
Dim LB As New Collection
LB.Add UserForm1.TextBox1
LB.Add UserForm1.TextBox2
LB.Add UserForm1.TextBox3
LB.Add UserForm1.TextBox4
LB.Add UserForm1.TextBox5
LB.Add UserForm1.TextBox6
LB.Add UserForm1.TextBox7
For i = 1 To 7
LB(i) = Worksheets("DATABASE-H").Cells(ComboBox1.ListIndex + 1, i)
Next i
End Sub

This code basically populates text boxes with the corresponding values referring to the value selected from combobox1

Now comes the problem part

Private Sub CommandButton1_Click()
ActiveCell.Offset(0, 0).Value = TextBox1.Value
ActiveCell.Offset(0, 1).Value = TextBox2.Value
ActiveCell.Offset(0, 2).Value = TextBox3.Value
ActiveCell.Offset(0, 3).Value = TextBox4.Value
ActiveCell.Offset(0, 4).Value = TextBox5.Value
ActiveCell.Offset(0, 5).Value = TextBox6.Value
ActiveCell.Offset(0, 6).Value = TextBox7.Value
Unload Me
End Sub

The above sub should be just inputting modified values in the sheet again at the corresponding positions but it dosent

I think its not activating cell corresponding to the value selected (on combobox)

Can someone please HELP!!

Hope this all makes sense.

Please do let me know if you need any further information

Any help would be much appreciated

Thanks in advance

Hello visitophir,

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.
3. You can also do this manually by placing the tag [code] at the start of the line, and the tag [/code] at the end.

As a member, You have agreed to follow the forum posting rules. Please take so time to familiarize yourself with the Do's and Don'ts here in the Forum, just click on the link below...

Forum Rules