How do I launch a Select Cell Dialogue Box ? 1

[lollyjar]

How do I open up a select cell dialogue box in excel to allow the users to select a specific cell (in much the same way the user can select a cell to reference against in conditional formatting)?

Cheers,

Lollyjar

 

[PHV]

Take a look at the Application.InputBox method and pay attention to the Type named parameter (8 is that you want)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lollyjar]

Thanks for the tip - you hit the nail on the head!

... however, I am getting an error when I try to assign the returned value to my range.

The code following returns the error (in MSAccess97) Runtime Error '424': Object required.

Am I just doing something silly to cause this?

Sub subTest()
    Dim myRange As Range
    Set myRange = Application.InputBox(prompt:="Sample", Type:=8)
End Sub

 

[PHV]

Set myRange = Application.InputBox(prompt:="Sample", Type:=8)
This must be an Excel application

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lollyjar]

Yes it is in Excel. Have you seen this before?

 

[PHV]

Try to Dim myRange as variant

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lollyjar]

If I use the following code, using variant, myRange actually returns the text that is in the cell, I guess as a string, instead of a range.

Sub subTest()
    Dim myRange As Variant
    myRange = Application.InputBox(prompt:="Sample", Type:=8)
    
End Sub

If I try to do the following and use 'set myrange = ...' I get a type mismatch - apparantly the InputBox object is not really returning a range. Any ideas?

Sub subTest()
    Dim myRange As Variant
    Set myRange = Application.InputBox(prompt:="Sample", Type:=8)
End Sub

 

[PHV]

I get the 424 error only when I click Cancel.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lollyjar]

I've tried a couple of PC's with an identical installation to mine and I do not get the error.

Would a re-install of excel possibly fix it?

Out of interest, I just found another thread that has come to a similar dead end ...

(for completeness, how do I insert a link to the other thread?)

Cheers,

Lollyjar

 

[PHV]

(for completeness, how do I insert a link to the other thread?)
Click on the Thread number, just below the title.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lollyjar]

Out of interest, also see thread181-27567 with a similar problem, at this time with no solution.

 

[IvanMoala]

Sub subTest()
    Dim myRange As Range
    Set myRange = Application.InputBox(prompt:="Sample", Type:=8)
    MsgBox myRange.Address
End Sub

Ivan F Moala

http://www.xcelfiles.com