Using Cell Address

[Stubby]

Hi

I'm sure there is an answer somewhere on the message board but I am having trouble finding it!!

Basically what I am trying to do is this:

I intend to have a userform that scans a spreadsheet and returns information from the sheet on the userform as checkboxes. A user will then have the ability to select a checkbox and the code will then insert a new row below the item that has been selected.

I can get the address of the Cell OK but I am having trouble using the address to add the new row.

I hope the above meakes sense!!

 

[Andrew Poulsom]

What have you tried that's causing trouble?

 

[Stubby]

What have you tried that's causing trouble?

I have the following code in a module:

Sub test()
Range("a4").Select
Do Until ActiveCell = "CAP"
ActiveCell.Offset(2, 0).Select
Loop
With UserForm1
.ChbTest1.Caption = ActiveCell.Value
.Tag = ActiveCell.Address
.Show
End With

End Sub

Then if the user clicks the checkbox and then the OK button I have the following code behind the OK button:

Private Sub CmdOK_Click()
Dim mYvar As Range
With UserForm1
If .ChbTest1.Value = True Then
Set mYvar = .Tag
mYvar.Offset(5, 0).Select
End If
End With

End Sub

 

[Weaver]

What have you tried that's causing trouble?

are you using a refedit box to get the cell reference?

If so, say it's called refCell

try range(refCell.value).offset(1,0).entirerow.insert shift:=xldown

 

[Andrew Poulsom]

Is this your problem?

Set mYvar = .Tag

Try:

Set mYvar = Range(.Tag)

 

[Stubby]

Is this your problem?

Set mYvar = .Tag

Try:

Set mYvar = Range(.Tag)

Thats exactly what I was looking for!!

I know it had to be something simple. I think thats my greatest downfall, I'm always thinking too complicated.

Many thanks

 

[Stubby]

are you using a refedit box to get the cell reference?

If so, say it's called refCell

try range(refCell.value).offset(1,0).entirerow.insert shift:=xldown

I'll bear this one in mind.

Many thanks