VBA InputBox user select range with reference

[nd0911]

Hello,

I want to let the user to select a range with reference of his choice.

I have this line of code

Set Rng = Application.InputBox(MyMsg, , , , , , , 8)

When the inputbox is shown the user can press F4 key to change the reference type to what he want, but how can I get the reference type ?

 

[nd0911]

A range does not have a reference type, a range address has a reference type.
An input box with type:=8 returns a range, not a range address. The dialog box will show the range's address (with the absoute/reative addressing controled by F4), but what is returned is the range that the user selected, not the address.
Ranges do not have reference styles. Range addresses have reference styles. An InputBox with type:=8 does not return range addresses.

Yes I know that, so what I need is to get the user range as a string with the reference type.

 

[nd0911]

find a solution:

str = Application.InputBox("bla bla", , , , , , , 0)
str = Application.ConvertFormula(str, xlR1C1, xlA1)

 

[Rick Rothstein]

I am not sure why you showed that last code line as it is converting a formula in R1C1 notation to A1 notation which is not what you described you wanted to do. Assuming your written description is actually what you want to do, see if the example macro does what you want...

Sub Test()
  Dim Rng As Range, Txt As String
  Set Rng = Application.InputBox("Please select a range..", Type:=8)
  Txt = InputBox("Please remove $ sign from any refrence that should be relative...", , Rng.Address)
  
  MsgBox Txt
  
End Sub

 

[mikerickson]

I noticed that the type of the input box changed to 0, which is great for entering formulas and where the F4 key for abs/rel is relevant. And it provides a string that can be used by ConvertFormula.