PDA

View Full Version : [SOLVED] Stoping a workbook from opening when macros are disabled?



kane
03-04-2005, 02:16 PM
Can you stop a workbook from opening when macros are disabled and give a messagebox to the user saying, "To enter the workbook please enable your macros."

And can you name a workbook after the contents of cell "A1"?

Zack Barresse
03-04-2005, 02:29 PM
Hi Kane, welcome to VBAX!

Addressing your questions seperately as 1 and 2 ...


Question 1:
You can't really. Not with code. What you can do is do some (of what I call) 'backwards programming'. Examples can be found ..

http://j-walk.com/ss/excel/tips/tip100.htm

http://www.mrexcel.com/board2/viewtopic.php?t=121978

http://www.mrexcel.com/board2/viewtopic.php?t=101300

Question 2:
You can use something like this ..


Sub RenameWorkbookAfter_A1()
Dim fPath As String
fPath = ThisWorkbook.FullName
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & Sheets("Sheet1").Range("A1").Value
Kill fPath
End Sub

Note: If you DON'T want to delete the original copy of the file (prior to saveAs) then delete the Kill .. line (last line of code).


HTH

mdmackillop
03-04-2005, 02:36 PM
A simple method to try, but not a high level of security; hide or password protect the sheets with a BeforeClose macro which requires an OnOpen macro to restore the sheets for use. If you need code for this, let us know.

To name the book, try


MyDir = "C:\ATest\"
MyFile = Sheets("Sheet1").Range("A1").Text
ActiveWorkbook.SaveAs Filename:=MyDir & MyFile

Zack Barresse
03-04-2005, 02:52 PM
Which, btw, these methods could fail if the file already exists and you choose to cancel out of the save. You can code around that though, depending on if you want to overwrite or not..

kane
03-04-2005, 03:38 PM
Wow, thanks. Both problems solved. This is a great service. Keep up the good work.

Zack Barresse
03-04-2005, 03:44 PM
Great, glad it works for you! I'll go ahead and mark this solved. You can do so on your posts by going to Thread Tools --> Mark Thread Solved --> Perform Action. :yes


Take care!