PDA

View Full Version : Worksheet with alternative calculation methods



K. Georgiadis
10-12-2007, 08:21 AM
Quite a while ago, I got help here with the attached workbook that selects either Automatic Calculation or Manual Calculation by means of two option buttons.

I want to adapt this technique for another application but it has been so long and I find that I no longer remember how the following code for the option buttons actually works:

Private Sub OptionButton1_Click()
' Auto populate
For i = 5 To Range("A65536").End(xlUp).Row
If Left(Range("A" & i), 4) = "Auto" Then
Rows(i & ":" & i).EntireRow.Hidden = False
Rows(i + 1 & ":" & i + 1).EntireRow.Hidden = True
Range("G4") = True
End If
Next
End Sub

Private Sub OptionButton2_Click()
' Manual entry
For i = 5 To Range("A65536").End(xlUp).Row
If Left(Range("A" & i), 4) = "Auto" Then
Rows(i & ":" & i).EntireRow.Hidden = True
Rows(i + 1 & ":" & i + 1).EntireRow.Hidden = False
Range("G4") = False
End If
Next
End Sub


Would you mind terribly deconstructing these two lines for me?


Why "For i =5" ?
If Left(Range("A" & i), 4) = "Auto" Then... Where is it finding "Auto" and why is this line in code the same in both the Auto and Manual option buttons?I really appreciate your help (and your indulgence).

lucas
10-12-2007, 09:04 AM
Hi K,
The first line:
For i = 5 To Range("A65536").End(xlUp).Row
means it starts on row 5 and finds the last row below row 5

The other part is a toggle:
If Left(Range("A" & i), 4) = "Auto" Then
that part is the same in both but look at the 3 lines below this statement in each macro. One sets to true and the other sets to false. Opposite each other.

It basically looks to see if it's set to Auto and then does different things according to your choice.

K. Georgiadis
10-12-2007, 10:14 AM
Thanks Lucas. I'm still not entirely clear where it locates the condition "Auto." I only see "Auto" as part of the text string "Auto-populate" in the first option button. Is that it?

lucas
10-12-2007, 11:57 AM
Yes I believe it is looking at the first 4 letters of that string....

K. Georgiadis
10-12-2007, 12:17 PM
I think I found it: it looks down column A for a text string whose first 4 left characters are "Auto," in this case: "Auto calculated ordering schedule" in A13, A22, A30, etc. Removing the prefix "auto" from the 1st option box has no effect on the macro, but removing "auto" from the labels in the above-mentioned rows prevents the macro from running.

lucas
10-12-2007, 02:52 PM
That is correct. Sorry I wasn't clearer....

Cyberdude
10-14-2007, 12:40 PM
Since you are going to be using the Application.Calculation statement, I strongly advise you to make use of a macro similar to the following in a Workbook_Open macro:
Sub CheckCalcStatus_Locator() ?Use this to test the next macro
Call CheckCalcStatus("Open", ThisWorkbook.Name)
End Sub

Sub CheckCalcStatus(OpnCls As String, WkbkNm As String) '10/14/07
'Arg "OpnCls" must have 1 of 2 values: "Open" or "Close"
Dim Msg As String, SubNm As String, OpnClsLit As String
Dim Title As String: Title = "'" & WkbkNm & "' [CheckCalcStatus]"
If OpnCls = "Open" _
Then
SubNm = "Workbook_" & "Open"
OpnClsLit = "opened." ?For use in message
Else
SubNm = "Workbook_" & "BeforeClose'"
OpnClsLit = "closed."
End If
If Application.Calculation = xlManual _
Then
Application.Calculation = xlAutomatic
Msg = "Calculation found set to Manual" & vbCr & _
"when workbook '" & WkbkNm & "' was " & OpnClsLit & vbCr & _
"It has been reset to Automatic."
MsgBox Msg, vbInformation, Title
End If
End Sub ?CheckCalcStatus?
This macro should be placed in the Workbook_Open macro in a manner similar to the following:
Private Sub Workbook_Open() '10/14/07
Call CheckEventsStatus("Open", ThisWorkbook.Name)
End Sub
Also add it to the Workbook_BeforeClose macro:

Private Sub Workbook_BeforeClose(Cancel As Boolean) '10/14/07
Call CheckCalcStatus("Close", ThisWorkbook.Name)
End Sub 'Workbook_BeforeClose'

This macro will guarantee that you don?t inadvertently leave the Application.Calculation status as ?Manual?.

K. Georgiadis
10-14-2007, 01:32 PM
Thanks Cyberdude.