Please check my code?

[Rich]

Hello,
I think I am nearing completion of a little VBA project I have been
working on to 1st: Force macros to run, by hiding all sheets except a
sheet called "Welcome", and 2nd: check the date entered on another to
be hidden sheet called "Data" against the 'Now' date. If incorrect,
then workbook closes after vbOk dialogue.

I am still learning, and at the moment I am having a mental meltdown
over why my code isn't doing a few things that I thought it would
have. Please examine:

Sub workbook_before_close()
Dim ws As Worksheet
ActiveSheet.Select
For Each ws In Worksheets
ws.Visible = xlVeryHidden
Next ws
Call Show_Welcome
End Sub

Sub workbook_open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Visible = True
Next ws
Call Hide_Datasheet
Call Hide_Welcome
If Now >= Worksheets("Data").Range("A1") Then
Confirm = MsgBox("This workbook has expired. Please contact
Suppport for further assistance.", vbInformation + vbOKOnly, "Workbook
Expiry")
If Confirm = vbOKOnly Then
Application.Quit
Else: Application.Quit
End If
End If
End Sub

Sub Hide_Datasheet()
Worksheets("Data").Visible = xlVeryHidden
End Sub

Sub Show_Welcome()
Worksheets("Welcome").Visible = xlSheetVisible
End Sub

Sub Hide_Welcome()
Worksheets("Welcome").Visible = xlVeryHidden
End Sub

I know by looking at it it probably isn't the most efficient looking
code. May I kindly request someone fro this talented arena looking
over the above code, and entering comments where you think it could be
better (or fixed?) If you could avoid it, please don't enter too much
new code as that would just confuse me (Unless you belive it is really
required!)
Thank you for your help, it is greatly appreciated.

p.s. all subs are under the ThisWorkbook section

 

[Chris Shipley]

Here are some thoughts (hopefully not confusing) to consider:
1. I'm not sure I understand "Force macros to run, by hiding all
sheets..."
2. Looking at the code, I don't think that VBA knows what to do with the
word 'Now'. Remember that if you were using a function it would be written
with empty parentheses as 'Now()'. Even that won't work in VBA though. One
way to get the value of Now() is to place the formula into a cell, place the
value of the cell into a variable, and then optionally clear the formula out
of the cell.
Cells(1,1).FunctionR1C1 = "=Now()"
sglCurrentDateTime = Cells(1,1).Value
Cells(1,1).Clear
3. After your messagebox, you have a conditional block that says: [if
'ok' then quit, else quit].
Why bother, especially when the user has no other option but to click 'Ok'?
4. Use parameters to reduce 4 functions to one:
Sub ShowHide_Sheet(ByVal aSheet as string, ByVal aVisible as Double)
Worksheets(aSheet).Visible = aVisible
End Sub
Call the function with:
Call ShowHide_Sheet("Data", xlVeryHidden)
Call ShowHide_Sheet("Welcome", xlSheetVisible)
5. Saying [Worksheets("Data").Range("A1")] is fine in excel vba. If you
want to move on to vb.net at some point, try to avoid shortcuts and say
[Worksheets("Data").Range("A1").Value]
6. Try spelling 'suppport' with only 2 p's -P)
Hope this helps
Chris

 

[Richard Alexander]

Quote:
"1. I'm not sure I understand "Force macros to run, by hiding all
sheets..."
- Here when a workbook is opened, only one sheet is available, because
before saving, a macro forced all other to be VeryHidden. The only way
to unhide is to enable macros.
"2. Looking at the code, I don't think that VBA knows what to do with
the
word 'Now'"
It actually does know what to do with Now, that is one of the codes that
actually works!

The part of the code that I think that doesn't work, is the part where I
try to loop through all sheets to determine which ones are hidden, and
which ones aren't.

may i ask if someone again, could quote my code as I have posted it, and
add there own comments in between the code to tell me where the
potential problems could be?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!