Solved

Call Excel subroutine from within a spreadsheet

Posted on 2011-09-04
9
265 Views
Last Modified: 2012-05-12
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 ???.
0
Comment
Question by:Dale Fye (Access MVP)
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 33

Assisted Solution

by:Norie
Norie earned 200 total points
ID: 36480856
Well the easiest way would be to just add a command button on a worksheet.

Then you can call the subroutine from the button's click event.

How to do that exactly depends on how you add the button.

(You could actually add a shape if that would be easier/more user friendly).

Other methods would be:

1 Add to the ribbon, which isn't too straightforward.

2 Use some event of the worksheet to trigger the code.

Which of these to use and how exactly to use them depend quite a bit what the subroutine does.
0
 
LVL 81

Accepted Solution

by:
byundt earned 300 total points
ID: 36480898
Further to imnorie's suggestions:

To trigger a macro from a shape object:
1) Put a shape (or picture) on the worksheet
2) Right-click the shape and choose "Assign macro" from the resulting pop-up
3) Choose your macro

To trigger a macro from a Forms toolbar command button (my recommendation):
1) On the Developer tab, use the Insert menu item and choose a Form controls command button
2) Draw the button on the worksheet
3) You will be asked to assign the button to an existing macro. Choose the one you want to run.

To trigger a macro from an ActiveX toolbar command button:
1) On the Developer tab, use the Insert menu item and choose an ActiveX controls command button
2) Draw the button on the worksheet
3) Right-click the button and choose View Code
4) Call your macro from the Click event sub for the button
0
 
LVL 33

Expert Comment

by:Norie
ID: 36480916
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.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36480942
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
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 81

Expert Comment

by:byundt
ID: 36480950
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
0
 
LVL 33

Expert Comment

by:Norie
ID: 36480967
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
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36480986
>>3 What's a Macintosh?:)

It is a delicious fruit, although truth be told I prefer the Fuji and Honey Crisp varieties :)
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36481465
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
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 36504382
Thanks,

I was able to put a command button on the form and do it that way.  Worked like a charm.
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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

910 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

23 Experts available now in Live!

Get 1:1 Help Now