Link to home
Start Free TrialLog in
Avatar of Dale Fye
Dale FyeFlag for United States of America

asked on

Call Excel subroutine from within a spreadsheet

I've written a subroutine that performs some data manipulation on two spreadsheets within a workbook.  I can run this subroutine from the VBA window or from the developers tab in the ribbon, but the individual who is going to be using this spreadsheet does not have the developers tab displayed.

I'm not an Excel guy, so I need a recommendation on possible techniques for providing the user with a fool proof method of kicking off the subroutine, either from the ribbon or a control on one of the spreadsheets, or ???.
SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Norie
Norie

byundt

I've seen a lot of recommendations of using forms over ActiveX but I've never really understood why.

I've always used ActiveX and never seem to have problems.

Probably just a hangup from other applications where you don't get the choice.

Have I been missing some big secret all these years?

PS Sorry ot bother you, just curious really.
imnorie,

I tend to avoid putting controls on the worksheet for aesthetic reasons.  If I have to put controls on the worksheet, I try to use only Forms controls, which seem to be much more stable.

The one tie a customer forced me to use ActiveX controls, I kept getting squirrely behavior in my listboxes: sizes would change, and font settings would change, for no reasons I could discern.  It was very, very discomfiting.

There is another method: the old Application.CommandBars object model still exists (Ribbon notwithstanding); if you use those older commands to add menu elements to the Worksheet Menu Bar, your menu items will appear in the Add-Ins tab of the Ribbon.  So far, all of my old menu code still works via that back door.

Patrick
I personally haven't had any problems with ActiveX controls, but zorvek claims to have seen more than his share.

I prefer the Forms command buttons because:
1) You may have multiple buttons that all call the same macro
2) The macro goes in a regular module sheet, meaning that Askers don't get confused by the need to put the code for an ActiveX button in the worksheet code pane
3) They are Macintosh compatible

I do use some of the other ActiveX controls (not command buttons) because of the greater flexibility offered. For example, ActiveX ComboBox controls return the user's choice directly (rather than through an index number like the Forms toolbar equivalent).

Brad
Patrick

I try and avoid it to, but I'm afraid I tend to jump to userforms probably more than is necessary.

Again probably just a habit.

I've heard and seen the resizing thing but for some reason I've never had major trouble with that.

Brad

1 Good point.

2 Another good point.

3 What's a Macintosh?:)

Cheers

Norie
>>3 What's a Macintosh?:)

It is a delicious fruit, although truth be told I prefer the Fuji and Honey Crisp varieties :)
The resizing issue is the only problem I've had.  I've stopped using ActiveX buttons, which previously was my default (for the reasons mentioned), though list objects (as you might imagine, given my articles) are great, to me.  Here's a tip for those "hard and fast" users of activex controls having resize issues - and its more of a "fix": http:/A_5315.html
Avatar of Dale Fye

ASKER

Thanks,

I was able to put a command button on the form and do it that way.  Worked like a charm.