Any Excel geeks in the house ?

D

dick_dastardly

Established member
Messages
843
Likes
152
Hope someone can help.

I have created a spreadsheet which I need to send to someone as an email attachment.

The spreadsheet uses macros and I want to make sure that when the user opens the spreadsheet it doesn't open up with VB edit mode set to ON.

I have tried sending the spreadsheet with VB edit mode switched OFF but for some reason the user keeps opening up the spreadsheet with edit mode ON ( so none of the buttons/macros work )

Does anyone know if it is possible to preset a spreadsheet with VB edit mode DISABLED ?

Sending the user instructions how to disable VB edit mode isn't really an option as it took me a whole bloody week just to get them to click the ENABLE MACROS option when opening the spreadsheet :mad:

Thanks

dd
 
hmmmmmm . . . .

you mean that when to workbook is opened, you are in design mode?
 
Last edited:
A Dashing Blade said:
hmmmmmm . . . .

you mean that when to workbook is opened, you are in design mode?
Click to expand...

Yes, that's right.

I could talk them through it over the phone ( how to switch design mode off ) only problem is, dozens of people will be using the spreadsheet so that's not really practical.

I don't want to end up as a sort of unpaid 24hr IT help line if you know what I mean, besides, I'm middle aged and don't do recreational drugs anymore so I'm not really qualified :cheesy:

dd
 
Poked through some of the older spreadsheets where this was resolved and that didn't spark the old walnut into action.

Sorry!
jj
 
A Dashing Blade said:
possibly relevent threads . . .
Opening not in design mode [Archive] - MrExcel Message Board
Control Design Mode - Excel Help & VBA Help

Obvious questions
Click to expand...

1) are you compiling the project before saving
Click to expand...

The word compile rings a bell from programing in assembler at college years ago.

I didn't know I had to do this so obviously no, I'm not compiling before saving. I was just coming out of design mode and then saving it. I'll search Excel HELP using COMPILE.

2) what version of excel
Click to expand...

2000

3) does this happen to all users
Click to expand...

Has happened to two users out of a total of four so far ( don't know if the other two have tried to run it yet ).

4) have you tried rebuilding the spreaddy?
Click to expand...

No, I'll do that if last if nothing else works

I think I've got enough to chew on for now. I'm in work right now and there's nowt doing so I'll play around with it until the morning :D

Thanks for all the advice guys


dd
 
dick_dastardly said:
The word compile rings a bell from programing in assembler at college years ago.

I didn't know I had to do this so obviously no, I'm not compiling before saving. I was just coming out of design mode and then saving it. I'll search Excel HELP using COMPILE.
Click to expand...

I didn't know either :eek:

What does compiling do in VBA? I don't have the problem with my worksheets opening up in design mode.
 
In the VBE menu, Debug . . . Compile Project

VBA is only compiled when the code is used for the first time eg modMain will only be fully compiled when any sub or function in modMain is called. (There appears to be an exception that prooves the rule with the Worksheet_Open event and global variables not being instantiated) but that's another story

Tbh, as this is a prob that only appears for certain (and more than one) users, this problem could take 10mins or 10 weeks to fix and the cause of the issue may never be fully resolved (trust me, loads of stuff goes on under the hood with excel).

Best to, if not too much of a problem, rebuild the spreaddy from scratch (cut & pasting the cosde is ok).
 
A Dashing Blade said:
In the VBE menu, Debug . . . Compile Project

VBA is only compiled when the code is used for the first time
Click to expand...

Yes, that's right, it's coming back to me now.

I remember when we wrote a prog in assembler we had to run the prog in 2 pass mode.

I think the first pass translated the prog into machine code and the second pass executed the prog or something like that.

Anyway, strange that this problem should suddenly pop up now. Like new_trader I've never encountered this problem before.

I'll do as you suggest an try cutting and pasting everything into a new workbook, see if that sorts it.

cheers

dd
 
dick_dastardly said:
Yes, that's right, it's coming back to me now.

I remember when we wrote a prog in assembler we had to run the prog in 2 pass mode.

I think the first pass translated the prog into machine code and the second pass executed the prog or something like that.

Anyway, strange that this problem should suddenly pop up now. Like new_trader I've never encountered this problem before.

I'll do as you suggest an try cutting and pasting everything into a new workbook, see if that sorts it.

cheers

dd
Click to expand...

If your code is in a module you can export it, then import it into the new workbook. That's the way I do things. You need to cut/copy and paste code from the worksheet though.
 
dick_dastardly said:
Hope someone can help.

I have created a spreadsheet which I need to send to someone as an email attachment.

The spreadsheet uses macros and I want to make sure that when the user opens the spreadsheet it doesn't open up with VB edit mode set to ON.

I have tried sending the spreadsheet with VB edit mode switched OFF but for some reason the user keeps opening up the spreadsheet with edit mode ON ( so none of the buttons/macros work )

Does anyone know if it is possible to preset a spreadsheet with VB edit mode DISABLED ?

Sending the user instructions how to disable VB edit mode isn't really an option as it took me a whole bloody week just to get them to click the ENABLE MACROS option when opening the spreadsheet :mad:

Thanks

dd
Click to expand...

Hey DD,

You could try to disable the calculation function - dont know if it will solve your problem but give this a go...

Tools...Options..Calculation Tab..

Unclick the auto calc button and save

Each time you open Excel and change a formula etc, you have to hit F9 to re-calc.

Hitting Alt +F11 will bring up the VBA programming section.

You might have to ask all your users to open a blank excel file and disable the auto calc on their own pc's and each time they open excel, the auto calc will be turned OFF until your users re-set it again, just remember to hit F11 to re-calc

cheers,
Leonie

cheers,
Leonie
 
If this hasn't been solved then I think I know what the problem is because I saw it happen! If the security settings are on medium you will be prompted to 'enable' macros, if the security settings are on high it will open in design mode.
 
new_trader said:
If this hasn't been solved then I think I know what the problem is because I saw it happen! If the security settings are on medium you will be prompted to 'enable' macros, if the security settings are on high it will open in design mode.
Click to expand...

Yes that could be it. Thinking about it, this problem only occurs on PC's at work and I imagine the security settings on the office PC's would be set at high.

I haven't really had much of a chance to look into this issue because the original problem that the spread sheet was designed to sort out keeps changing all the time ( I'm up to the 5th version of the spread sheet so far ).

Looking back I think it would have been less labour intensive if I had just continued with paper, pencil and calculator rather than designing a spread sheet to sort the problem.

But as the saying goes, I've started so I'll finish.

Thanks Chaps

dd
 
dick_dastardly said:
Yes that could be it. Thinking about it, this problem only occurs on PC's at work and I imagine the security settings on the office PC's would be set at high.
Click to expand...


We must work at the same place then :-0

That's where it happens. Each person using the spreadsheet must set their local Excel security settings to medium so they can open it with macros.
 
I was going to ask this Q on xtremevb.com but for some reason I don't have privilages to post there anymore ( maybe a mod there has been reading my posts here :cheesy: ).

Anyway. Does anyone know what the VB command to clear ( empty ) the contents of every cell on a specific worksheet is?

A command similar to " CLS " ( clear screen ) which I remember from my Acorn Electron days.


dd
 
You must log in or register to reply here.

Similar threads

L
Dyman & Associates Risk Managements Projects: 8 Tips for Keeping Spreadsheets Secure
Replies
0
Views
1K
lloiselaurel
L
M
Calculate the Value of the FTSE
Replies
19
Views
7K
Captain Haddock
C
J
FTSE 100 data feed for excel and setup
Replies
2
Views
7K
A Dashing Blade
A Dashing Blade
Y
position sizing calculator for forex strategies
Replies
0
Views
8K
ybfjax
Y
E
Detecting when a futures contract is with the Bloomberg Excel plugin
Replies
2
Views
4K
A Dashing Blade
A Dashing Blade
Top