Control source using variables 1

[ChrisBurch]

Excel 97

I'm trying to set the control souce for a textbox (on a userform), using named ranges and R1C1 convention.

eg. A2 as a ControlSouce works okay, but I'm trying to set it using R1C1 convention, which would be (2,1) and then use named ranges for R & C, so that I end up with something like ([RowNum],[ColNum]). However, every conbination I try generates an 'Invalid property value' error.

There must be a way of using R1C1 referencing, can anyone point me in the correct direction?

Thanks

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[Zathras]

Experiment with this:
[blue]

Private Sub CommandButton1_Click()
Dim nRow As Long
Dim nCol As Integer
  nRow = TextBox1.Value
  nCol = TextBox2.Value
  TextBox3.ControlSource = Cells(nRow, nCol).Address
End Sub

[/color]

 

[ChrisBurch]

Thanks Zathras, that got me going in the right direction.

Chris

It worked yesterday.
It doesn't work today.
That's Windows!