Solved

Confused Over Modules & Individual Sheet Code

Posted on 2013-01-10
5
239 Views
Last Modified: 2013-01-18
A lot of confusion on my part here .....

Should I keep all my code in modules or is good practice to keep some code within a sheet?

When should I use form controls (eg buttons) and when should I use ActiveX buttons?

When I create code by recording a macro, I'm pushed into using form controls? Can I change form controls to an activeX controls, or should I copy recorded code into a new procedure?

Specifically, what should I do with my Sub DeleteData() (some of which came from a macro recording) ?
Module CodeSheet Code
0
Comment
Question by:Eirman
  • 2
  • 2
5 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 300 total points
ID: 38764369
You should use (the IMO poorly named) form controls on a worksheet and ActiveX control on a Userform. You can however use ActiveX forms on a sheet but it's not recommended. If you do do that, put the code (or cut and paste it) in the button's Click event.

If the same code is executed from more than one place the code should be in a Public Sub or Function in a Module, otherwise it's okay in the sheet, workbook or userform.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38764794
Hi, Eirman.

Couple of points...
(1) Your macro shows symptoms of the Macro Recorder. The following two lines...
Range("B10:BK10").Select
Selection.ClearContents

...should be replaced by....
Range("B10:BK10").ClearContents
(2) It's often considered good practice, where possible, to put macros in Modules.

Regards,
Brian
0
 
LVL 23

Author Comment

by:Eirman
ID: 38766550
Thanks for the code simplification Brian.

I'm still not sure what to do regarding my specific query. I've got a two Buttons on the ESB-2 sheet....
One is a command button where I can use click or double click.
The other is a form button where I don't appear to have the same flexibility.
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 200 total points
ID: 38766924
Eirman,

The ActiveX button's code will be in the Sheet. The Form button can be in either place, but I would put it in a module.

(BTW, the code changes are not just a simplification - the main reason for them is that they are faster.)

Regards,
Brian.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38793435
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

707 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

12 Experts available now in Live!

Get 1:1 Help Now