MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA - modules -vs- sheet


Posted by Jason Davis on October 20, 2001 11:23 AM

When entering code in a module:
eg.
sheet("raw").select
range("a1").select

The code selects the raw worksheet and moves the focus or selection to range a1.

Why does this not work if you are on entering code in a "sheet"?
Is it because "sheet" only applies to the worksheet you are on? Or is there another format to select a different worksheet and move the focus to a cell?

I can change the value of any cell on another worksheet but not move the focus.

Confused.


Posted by Robert Charlton on October 20, 2001 2:39 PM


Try this :-

Sheets("raw").Select
Sheets("raw").Range("a1").Select

Or :-

Sheets("raw").Select
ActiveSheet.Range("a1").Select


It is normally not good practice to put normal macros in sheet code modules (normally better to use them for event procedures only).

Also, why do you need to actually select A1 on "Raw" ?
When writing macros it is good practice to avoid selecting and activating (which can nearly always be avoided). Selecting/activating makes the macro slow.


Posted by Jason Davis on October 22, 2001 6:53 AM

I am using Excel as a HMI for a automated calibration system. It controls a PLC and a DAS unit. I have been trying to keep it simple so others will be able to maintain/modify it for custom applications. Since I cant get a allocation of funds for WonderWare software, Excel it is. The selection focus change is only to put the user at the top of the spreed sheet so they can see the buttons.

Posted by Robert Charlton on October 22, 2001 2:19 PM


Try freezing the top rows where the buttons are so that they will stiil be seen when scrolling down.

Posted by Jason Davis on October 23, 2001 3:11 AM

Good Idea, hadn't thought of that.....thank you