We help IT Professionals succeed at work.

Using a control to return a multi-sheet range - current refedit kludge code for comment

Dave
Dave asked
on
307 Views
Last Modified: 2012-05-08
Gents,

Some time back I developed what I think is a kludgey workaround to use the multi-sheet address potentially returnable by the famously error prone RefEdit box. My workaround that to parses out the sheet range is attached

Has anyone come across a better solution?

The Application.Inputbox approach is local to a single sheet so this won't do

Regards

Dave

refedit.xls
Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
Thanks Stellan for your input
But I do need a control that can be clicked on so that the user can select a 3D range
Regards
Dave
CERTIFIED EXPERT

Commented:
But my solution behaves like the refedit control by using an event handler for updating the textbox. So you click on the cells and/or sheets and the range information is read by a vba procedure. The only visible difference is that I did not add the code to show the selected sheets when user is selecting the range. That could easily be added.
Am I missing something here?

Kind regards,
Stellan
CERTIFIED EXPERT

Author

Commented:
Stellan,
My bad... I couldn't see that a multi-sheet selection had been enabled, as the tab colour hadn't chnaged
I think this is a better workaround than my direct refedit parsing, other than a second box will be needed to show the user the sheet range
I will leave this open for 24 hours to see if anyone else has an approach, I will award you points
Thanks
Dave
CERTIFIED EXPERT

Commented:
Hi Dave,
I am glad you like it. Here is an improved version where the selected sheets are displayed in a label control. That information is updated when the user select cells. I cannnot find any event that is triggered when the user changes only the sheet selection so I use a workaround using OnTime to refresh the displayed info.

Of course there are some more details needed. For example if there are other sheets than worksheets or if user selection is not a range.

Kind regards,
Stellan
Alternative-RefEdit.xls
CERTIFIED EXPERT

Commented:
I made some more improvements here. Please disregard the last sample file.

/Stellan
Alternative-RefEdit-2.xls
CERTIFIED EXPERT

Commented:
Strange...
it looks like you have to save the file first. When I open it directly from the link it will try to open a second copy to run the OnTime procedure. I must admit that I do not understand that behaviour (yet).

Kind regards,
Stellan
CERTIFIED EXPERT

Author

Commented:
Stellan,
Thx for this - a fresh pair of eyes certainly helped
Regards
Dave
CERTIFIED EXPERT

Author

Commented:
thx
CERTIFIED EXPERT

Commented:
Dave,

I enjoyed this question. Thank you for that
and for the points too.

Kind regards,
Stellan

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.