VBA problem I can't work out :(

omega66

New Member
Joined
Oct 3, 2006
Messages
9
Hi guys,

I'm new in VBA programming, so I'm really in need of help with this one. There's a load of EXCEL spreadsheets that my Boss wants me to protect with VBA code (formulas and links). Meaning he wants me to design user forms for data input, while the Application itself would be hidden (application.visible=false). It's all ok and the task is rather simple. The question I have is, how do I prevent users from viewing spreadsheets by disabling Macro's? With disabled macro's VBA code can't run - therefore all sheets are visible to users.

I'm so depressed; I can't find a solution to this problem. PLEASE help me. Oh, and I'm really sorry about my English - it’s been a long time since I’ve last used it. Thanks in advance for your kind response. :)
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Welcome to the board!

Your english is better than many native speakers, so please don't apologize.

There is no way to force the macros on startup. BUT. A quick and dirty solution is to build a dummy workbook that has nothing in it but a big button that will open the ACTUAL file, setting .visible = false. The idea is that the actual workbook is someplace where nobody would think to look, so they can't open it directly... they MUST use the dummy workbook. And if they open the dummy workbook without enabling macros, then they have no access to the actual workbook.

It's been a while since I have done this, so I can't remember the loop-holes that need to be closed... but my recollection is that they are few and easily solved by playing with the system...
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
Welcome to the board.

You cannot force users to enable macros. A common workaround is to have one sheet that says "you cannot use this file unless you enable macros" (and list the instructions to enable macros if you prefer). Save your workbook with that sheet visible and all others hidden (or xlveryhidden, if you prefer). The first part of your workbook_open event would be to unhide the appropriate sheets and hide the warning sheet.

Another suggestion is to sign your code and make yourself a trusted source on the other computers so the security problem is a non-issue.
 

omega66

New Member
Joined
Oct 3, 2006
Messages
9
Thanks guys, but I need something more solid. Forcing users to enable macros isn’t what I had in mind. The idea of hiding sheets is very appealing, but the question is would this solve my problem? Let’s say we hide some spreadsheets with ‘xlveryhidden’. Can’t the user unhide them manually? I guess they can. So the next step would be assigning passwords that would be solved with VBA code. The problem is, that when we want to unhide a password protected hidden spreadsheet, Excel opens a special UI for password input – is there any way to change this from happening? If so, then there’s still hope. And Hatman – thank you for saying my English doesn’t suck. I really respect that. Please stay with me on this one, ok? Thanks once again for helping me.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984

ADVERTISEMENT

If you use xlveryhidden, the user can only unhide the sheets using macros.
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
With xlVeryHidden you cannot make it visible manually
you can only make it visible using code or selecting VBA Propterties

code to Hide Sheet2:
Code:
Sub HideSHeet()
    Sheets("sheet2").Visible = xlVeryHidden
End Sub

code to UnHide Sheet2:
Code:
Sub ShowSheet()
If Application.InputBox("PASSWORD PLEASE", "PASSWORD", "") = "pass" Then Exit Sub
Sheets("sheet2").Visible = True
End Sub
 

omega66

New Member
Joined
Oct 3, 2006
Messages
9

ADVERTISEMENT

That’s right - xlVeryHidden is no ordinary hiding, is it? Ok. One more thing. Would it be possible to make a .xls file date dependent. Let’s say I need to limit the use of an Excel file. Would this be possible? Again, you have already helped me a lot. Thanks Oaktree and Datsmart. :)
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
One more thing. Would it be possible to make a .xls file date dependent.

Sure. Use something like this as part of your workbook open event:
Code:
 If Date > #12/31/2006# then 
msgbox "You cannot use this file" 
'rest of code to quit Excel or whatever here
end if
 

omega66

New Member
Joined
Oct 3, 2006
Messages
9
Hahahaha … ok. I really am an idiot. Sure.
:biggrin:
There’s no way I can thank you enough. I’ll pass on the word about how great your forum is. You’re the best. I’ll see you soon – must run and do some work, now that I know how (thanks to you).

Best regards,
Andrew
 

Watch MrExcel Video

Forum statistics

Threads
1,114,052
Messages
5,545,734
Members
410,702
Latest member
clizama18
Top