Any Excel geeks in the house ?

[dick_dastardly]

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

Thanks

dd

 

[A Dashing Blade]

hmmmmmm . . . .

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

 

[Mathemagician]

Man, I had this same issue a long while back. I'll try to remember the fix, but no promises...

jj

 

[new_trader]

Xtreme Visual Basic Talk - Powered by vBulletin

 

[dick_dastardly]

hmmmmmm . . . .

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

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

 

[dick_dastardly]

Man, I had this same issue a long while back. I'll try to remember the fix, but no promises...

jj

OK cheers

dd

 

[dick_dastardly]

Xtreme Visual Basic Talk - Powered by vBulletin

Thanks for the link, I'll have a look.


dd

 

[A Dashing Blade]

possibly relevent threads . . .
Opening not in design mode [Archive] - MrExcel Message Board
Control Design Mode - Excel Help & VBA Help

Obvious questions
1) are you compiling the project before saving
2) what version of excel
3) does this happen to all users
4) have you tried rebuilding the spreaddy?

 

[Mathemagician]

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

Sorry!
jj

 

[dick_dastardly]

possibly relevent threads . . .
Opening not in design mode [Archive] - MrExcel Message Board
Control Design Mode - Excel Help & VBA Help

Obvious questions

1) are you compiling the project before saving

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

2000

3) does this happen to all users

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?

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

Thanks for all the advice guys


dd

 

[new_trader]

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.

I didn't know either

What does compiling do in VBA? I don't have the problem with my worksheets opening up in design mode.

 

[A Dashing Blade]

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).

 

[dick_dastardly]

In the VBE menu, Debug . . . Compile Project

VBA is only compiled when the code is used for the first time

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

 

[new_trader]

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

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.

 

[zphinx]

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

Thanks

dd

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

 

[new_trader]

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.

 

[dick_dastardly]

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.

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

 

[new_trader]

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.

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.

 

[dick_dastardly]

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

 

[A Dashing Blade]

Thisworkbook.worksheets("Sheet1").Cells.clearcontents