Setting Boolean Using a Cell

[LordDragon]

Greetings,


I'm stuck on a Boolean setting.

I know I can set a Boolean Variable that the whole workbook can use and set it to either True or False to start, then let it change as needed.

However, I need it to remember the setting even if the workbook is closed and reopened.

So, I'm trying to set the value of my Boolean variable in a specific cell.

I am using the following code to do that.




Function SetPreviewOrderRun()


Dim PreviewOrderRunControl As Boolean


PreviewOrderRunControl = ActiveWorkbook.Worksheets("Rig Survey Form").Range("AG14")

PreviewOrderRunControl = True

End Function



The problem seems to be in that code as when I manually change the targeted cell to True, then the following code works fine.





Private Sub CirronetRadioCheck()


Dim PreviewOrderRunControl As Boolean

PreviewOrderRunControl = ActiveWorkbook.Worksheets("Rig Survey Form").Range("AG14")

If PreviewOrderRunControl = False Then
Call CirronetRadioIncrement
Call SetPreviewOrderRun
Else
Exit Sub
End If


End Sub



I have tried putting the SetPreviewOrderRun code in a Sub and in various places and to have it called from different places.

The rest of the code is:





Function CirronetRadioIncrement()


Dim CommCableAdd As Integer
Dim CirronetRadioAdd As Integer
' Dim PreviewOrderRunControl As Boolean


CommCableAdd = ActiveWorkbook.Worksheets("EDR").Range("D56").Value
CirronetRadioAdd = ActiveWorkbook.Worksheets("EDR").Range("D8").Value
' PreviewOrderRunControl = ActiveWorkbook.Worksheets("Rig Survey Form").Range("AG14")
' Call SetPreviewOrderRun

If ActiveWorkbook.Worksheets("EDR").Range("A8").Value = strYes Then
With ActiveWorkbook.Worksheets("EDR")
.Range("A56").Value = strYes
.Range("D56").Value = CommCableAdd + 1
.Range("D8").Value = CirronetRadioAdd + 1
End With
' PreviewOrderRunControl = True
End If


End Function



As you can see, I've tried several different ways to get the code to set the assigned cell to True or Fale.

By the way, the following code works too:





Function ResetPreviewOrderRun()


Dim PreviewOrderRunControl As Boolean

PreviewOrderRunControl = ActiveWorkbook.Worksheets("Rig Survey Form").Range("AG14")

If PreviewOrderRunControl = True Then
ActiveWorkbook.Worksheets("Rig Survey Form").Range("AG14").Value = False
Else
Exit Function
End If


End Function



I would appreciate some help please.

[Paul_Hossler]

I use the registry or something simple like a Name. These are in the WB Open and Close, but you could put them other places



Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.Names("Status").RefersTo = "=TRUE" Then
ActiveWorkbook.Names("Status").RefersTo = "=FALSE"
Else
ActiveWorkbook.Names("Status").RefersTo = "=TRUE"
End If

MsgBox "Status before Close is " & ActiveWorkbook.Names("Status").RefersTo

End Sub


Private Sub Workbook_Open()
Dim v As Variant

v = Null
On Error Resume Next
v = ActiveWorkbook.Names("Status").RefersTo
On Error GoTo 0

If IsNull(v) Then ActiveWorkbook.Names.Add Name:="Status", RefersTo:="=TRUE"
MsgBox "Status After Open is " & v
End Sub

[mikerickson]

You could put code like this in the ThisWorkbook module. After adjusting the ranges to suit.

' in ThisWorkbook

Property Get StaticBoolean() As Boolean
StaticBoolean = (CStr(Sheets("Rig Survey Form").Range("AG14")) = "True"):
End Property

Property Let StaticBoolean(inVal As Boolean)
Sheets("Rig Survey Form").Range("AG14").Value = inVal
End Property

And use it (in any module) with code like


If ThisWorkbook.StaticBoolean Then
MsgBox "Its True! Its True!"
Else
MsgBox "its not"
End If

ThisWorkbook.StaticBoolean = Not (ThisWorkbook.StaticBoolean)

[LordDragon]

Paul,

Thanks, I'll play around with this code and look further into this suggestion.

The idea though is to not have it linked to the workbook being opened. I want it to work after a certain code is run.

I'm having a problem with the incremental code I posted above running every time the user runs my "Preview Order" code. I had a user tell call me this week and say they realized they kept forgetting parts (which is the point of the Preview Order button) so they went back and made the changes, but the incremental code kept running every time they tried to preview again. This has ended up with those particular parts being incremented to 12 or higher. When it only needs to run once.

Again, all my code is working fine, except the code that is supposed to change the assigned cell to "True".

[mikerickson]

To combine my approach with Names, change the ThisWorkbook code to. It is used in the same way as above.

' in ThisWorkbook

Property Get StaticBoolean() As Boolean
On Error Resume Next
StaticBoolean = Evaluate(Me.Names("StaticBoolean").RefersTo)
On Error GoTo 0
End Property

Property Let StaticBoolean(inVal As Boolean)
Me.Names.Add(Name:="StaticBoolean", RefersTo:="=" & CStr(inVal)).Visible = False
End Property

[SamT]

However, I need it to remember the setting even if the workbook is closed and reopened.

Set the Cell's value in the SetPreviewOrderRun Sub and the ResetPreviewOrderRun Sub

Put this function in your global variables module.
Public Function PreviewOrderRunControl()
PreviewOrderRunControl = ThisWorkbook.Worksheets("Rig Survey Form").Range("AG14")
End Function

Anywhere you need it
If PreviewOrderRunControl Then

[LordDragon]

Yay! It works.

I realized what I did wrong with my code.

In the Reset code, I told it that if it was already True, set it to False, otherwise ignore it.

In the Set code, I simply told it to set it. I changed it to the same as the Reset code (but reverse) and it worked.

However, I also tried what SamT suggested and it works too, but with much less code.

Either solution will work though.