Solved

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

Posted on 2001-06-12
10
494 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

706 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

17 Experts available now in Live!

Get 1:1 Help Now