• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 563
  • Last Modified:

VBA/Excel 97: Application.Inputbox (...Type:=8)

Question: Can the trick of http://www.j-walk.com/ss/excel/tips/tip81.htm be modified to allow selecting a range on another sheet ? With other words: How to pause a macro to allow the user select a sheet by mouse in a open workbook (i.e. without asking for the filename/tabname...) ?

Double points if there is a solution for Excel 97 which can be used as a general procedure.
0
cri
Asked:
cri
  • 4
  • 3
  • 2
  • +1
1 Solution
 
forsbomCommented:
Hi cri
Didn't expect to find you in this section....
I have done something similar. I'm not sure if this works with Excel 97 (I'm using Excel 2000). I've added a RefEdit control in the References menu and created a form that uses it. This control let the user select a range from a sheet. It's called Refedit.dll. I found this in the Office installation directory.
If you like I can e-mail the dll to you to play around with.

Peter
0
 
blakeh1Commented:
FYI, the refedit control is available in 97 as well
0
 
blakeh1Commented:
you can have the method work if you change the following line
UserRange.Range("A1") = Output

to

UserRange = Output
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
blakeh1Commented:
Note: changing that line will output to all selected cells, not just the upper left cell in selected range, not sure if that matters or not. If it does use

UserRange.Cells(1, 1) = Output

instead
0
 
criAuthor Commented:
Thank you all, I will try this tomorrow.
0
 
criAuthor Commented:
Got it working, apparently the trick is to cascade the activation commands, this allows even to switch between workbooks.

Sub HowToGetUserRange()
    Dim UserRange As Range, sDlgTitle As String
   
    Prompt = "Select a range"
    sDlgTitle = "HowToGetUserRange"

    On Error Resume Next
    Set UserRange = Application.InputBox("Select range", sDlgTitle, ActiveCell.Address, Type:=8)
    If UserRange Is Nothing Then Exit Sub
   
    With UserRange
       MsgBox "Choice was: " & .Address & ", sh=" & .Parent.Name & ", wb=" & .Parent.Parent.Name, vbInformation, sDlgTitle
       .Parent.Parent.Activate 'change to selected workbook
       .Parent.Activate 'change to selected sheet
       .Activate 'change to selected range
       MsgBox "Chosen range now activated", vbInformation, sDlgTitle
    End With
   
End Sub

If somebody can show me till end of this week how to activate the choosen range _directly_ (i.e. not using the three-step above) s/he will bag the question.

Otherwise forsbom will bag it, despite the fact that I will not pursue this solution as I want to stay clear of forms and references which might not be available on other PC.

PAQ readers see also http://support.microsoft.com/support/kb/articles/Q158/4/02.asp for further help on RefEdit.


0
 
calacucciaCommented:
cri,

Try this

Application.Goto UserRange

calacuccia
0
 
criAuthor Commented:
calacuccia, "Try", my rear-end... >8o)

Doubling the stake as promised.

0
 
calacucciaCommented:
<Smile> and a <Wink>

ThAnks, cri.
0
 
criAuthor Commented:
Duh...

Forgot to thank the other participants.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now