I am trying to build a form to use for a custom public function that works
like the Insert Forms Dialog box (it appears that I can't just call and use
it from VBA). What is the right control to use to allow the user to indicate
the cell address. I am trying a RefEdit edit but it doesn't work the same as
the control in Insert Function and I am having trouble getting the selected
cell written into the formula as a cell reference. This doesn't do it.

ActiveCell.FormulaR1C1 = "=BU2Gp(" & ActiveSheet.Range(RefEdit1.Value) & ")"

Any advice is appreciated.
Thanks,
John

ActiveCell.FormulaR1C1 _
= "=BU2Gp(" & ActiveSheet.Range(RefEdit1.Value).Address & ")"

or maybe...

ActiveCell.FormulaR1C1 _
= "=BU2Gp(" & ActiveSheet.Range(RefEdit1.Value).Address(external :=true) & ")"

Just in case it's on another sheet (or workbook).


John Hutcins wrote:

I am trying to build a form to use for a custom public function that works
like the Insert Forms Dialog box (it appears that I can't just call and use
it from VBA). What is the right control to use to allow the user to indicate
the cell address. I am trying a RefEdit edit but it doesn't work the same as
the control in Insert Function and I am having trouble getting the selected
cell written into the formula as a cell reference. This doesn't do it.

ActiveCell.FormulaR1C1 = "=BU2Gp(" & ActiveSheet.Range(RefEdit1.Value) & ")"

Any advice is appreciated.
Thanks,
John

--

Dave Peterson

I had tried that and got an error. After seeing your response I changed
ActiveCell.FormulaR1C1
to
ActiveCell.Formula =
And that works. It writes the absolute reference ("$A$1") instead of the
relative reference ("A1") which I found I could change with
ActiveSheet.Range(RefEdit1.Value).Address(RowAbsol ute:=False,
ColumnAbsolute:=False) & ")"

Thanks, this is exactly what I was looking for!
John

"Dave Peterson" wrote:

ActiveCell.FormulaR1C1 _
= "=BU2Gp(" & ActiveSheet.Range(RefEdit1.Value).Address & ")"

or maybe...

ActiveCell.FormulaR1C1 _
= "=BU2Gp(" & ActiveSheet.Range(RefEdit1.Value).Address(external :=true) & ")"

Just in case it's on another sheet (or workbook).


John Hutcins wrote:

I am trying to build a form to use for a custom public function that works
like the Insert Forms Dialog box (it appears that I can't just call and use
it from VBA). What is the right control to use to allow the user to indicate
the cell address. I am trying a RefEdit edit but it doesn't work the same as
the control in Insert Function and I am having trouble getting the selected
cell written into the formula as a cell reference. This doesn't do it.

ActiveCell.FormulaR1C1 = "=BU2Gp(" & ActiveSheet.Range(RefEdit1.Value) & ")"

Any advice is appreciated.
Thanks,
John

--

Dave Peterson

Sorry, I didn't notice the .formulaR1C1.

There's another option in the .address() property that could make it so you use
R1C1 reference style.

John Hutcins wrote:

I had tried that and got an error. After seeing your response I changed
ActiveCell.FormulaR1C1
to
ActiveCell.Formula =
And that works. It writes the absolute reference ("$A$1") instead of the
relative reference ("A1") which I found I could change with
ActiveSheet.Range(RefEdit1.Value).Address(RowAbsol ute:=False,
ColumnAbsolute:=False) & ")"

Thanks, this is exactly what I was looking for!
John

"Dave Peterson" wrote:

ActiveCell.FormulaR1C1 _
= "=BU2Gp(" & ActiveSheet.Range(RefEdit1.Value).Address & ")"

or maybe...

ActiveCell.FormulaR1C1 _
= "=BU2Gp(" & ActiveSheet.Range(RefEdit1.Value).Address(external :=true) & ")"

Just in case it's on another sheet (or workbook).


John Hutcins wrote:

I am trying to build a form to use for a custom public function that works
like the Insert Forms Dialog box (it appears that I can't just call and use
it from VBA). What is the right control to use to allow the user to indicate
the cell address. I am trying a RefEdit edit but it doesn't work the same as
the control in Insert Function and I am having trouble getting the selected
cell written into the formula as a cell reference. This doesn't do it.

ActiveCell.FormulaR1C1 = "=BU2Gp(" & ActiveSheet.Range(RefEdit1.Value) & ")"

Any advice is appreciated.
Thanks,
John

--

Dave Peterson


--

Dave Peterson