VBA problem I can't work out :(

[omega66]

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.

 

[hatman]

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]

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]

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]

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

 

[Datsmart]

With xlVeryHidden you cannot make it visible manually
you can only make it visible using code or selecting VBA Propterties

code to Hide Sheet2:

Sub HideSHeet()
    Sheets("sheet2").Visible = xlVeryHidden
End Sub

code to UnHide Sheet2:

Sub ShowSheet()
If Application.InputBox("PASSWORD PLEASE", "PASSWORD", "") = "pass" Then Exit Sub
Sheets("sheet2").Visible = True
End Sub

 

[omega66]

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]

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:

 If Date > #12/31/2006# then 
msgbox "You cannot use this file" 
'rest of code to quit Excel or whatever here
end if

 

[omega66]

Hahahaha … ok. I really am an idiot. Sure.

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