Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

Confused Over Modules & Individual Sheet Code

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
Eirman
Asked:
Eirman
  • 2
  • 2
2 Solutions
 
Martin LissOlder than dirtCommented:
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
 
redmondbCommented:
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
 
EirmanChief Operations ManagerAuthor Commented:
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
 
redmondbCommented:
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
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now