Solved

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

Posted on 2001-06-12
10
503 Views
Last Modified: 2012-05-04
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
Comment
Question by:cri
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 3

Expert Comment

by:forsbom
ID: 6181376
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
 
LVL 6

Expert Comment

by:blakeh1
ID: 6181442
FYI, the refedit control is available in 97 as well
0
 
LVL 6

Expert Comment

by:blakeh1
ID: 6181503
you can have the method work if you change the following line
UserRange.Range("A1") = Output

to

UserRange = Output
0
 
LVL 6

Expert Comment

by:blakeh1
ID: 6181519
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
 
LVL 13

Author Comment

by:cri
ID: 6182288
Thank you all, I will try this tomorrow.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 13

Author Comment

by:cri
ID: 6185081
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
 
LVL 17

Accepted Solution

by:
calacuccia earned 200 total points
ID: 6185671
cri,

Try this

Application.Goto UserRange

calacuccia
0
 
LVL 13

Author Comment

by:cri
ID: 6186059
calacuccia, "Try", my rear-end... >8o)

Doubling the stake as promised.

0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6186104
<Smile> and a <Wink>

ThAnks, cri.
0
 
LVL 13

Author Comment

by:cri
ID: 6186242
Duh...

Forgot to thank the other participants.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Outlook Free & Paid Tools
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now