Address Reference for selection from Range - VBA

visitophir

New Member
Joined
Jun 5, 2008
Messages
25
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
 

visitophir

New Member
Joined
Jun 5, 2008
Messages
25
Thanks Guys for looking and thinking hard

I figured out lthe solution latter

Worksheets("DATABASE-H").Range("B" & UserForm3.ComboBox10.ListIndex + 1 + 1).Select

Addding this code I was able to generate dynamic reference which solved the issue

Thanks Anyways
 

Forum statistics

Threads
1,085,301
Messages
5,382,808
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top