Solved

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

Posted on 2001-06-12
10
533 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Outlook Free & Paid Tools
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

717 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