I am going to preface that I am a novice at best with VBA. I have an Excel 2016 spreadsheet that is being used as a database. I have declared a public variable, AllUpdated, to define whether or not the data in the spreadsheet is up to date. I have an ActiveX label that I want to show when the data is not updated and disappear when the data is up to date. However, the variable appears to only ever be False no matter what the user input is seeing as the label is always visible.
I declare the variable upon opening the workbook. The user is prompted to whether or not they want to refresh the data (it is linked to an Access database). If they select Yes, the variable is supposed to change to True and if No the variable goes False.
Public AllUpdated As Boolean
Sub Workbook_Open()
cRefresh = MsgBox("Would you like to refresh your data?", vbYesNo, "Refresh")
If cRefresh = vbYes Then
Call sRefreshMaster
AllUpdated = True
Else
AllUpdated = False
End If
End Sub
When the user selects a cell in the workbook, the label is supposed to appear if the data is out of date and disappear if the data is up to date.
'- Just some code I found online on how to show and hide items in a given spot
'- on the screen each time a new cell is selected
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyPicture As Object
Dim MyTop As Double
Dim MyLeft As Double
Dim BottomRightCell As Range
Set MyPicture = ActiveSheet.Shapes("OODWatermark")
If AllUpdated Then
'- if data is up to date, hide watermark
With MyPicture
.Visible = False
End With
Else
'- if data is out of date, display watermark
'-----------------------------------------------------------
'- bottom right cell
With ActiveWindow.VisibleRange
r = .Rows.Count
c = .Columns.Count
Set BottomRightCell = .Cells(r, c)
End With
'------------------------------------------------------------
'- position picture
MyTop = BottomRightCell.Top - MyPicture.Height - 5
MyLeft = BottomRightCell.Left - MyPicture.Width - 5
With MyPicture
.Visible = True
.Top = MyTop
.Left = MyLeft
End With
End If
End Sub
If I manually change the label to be Visible = False, the label will disappear until I click on a new cell, then the label will reappear. How can I fix this? I assume I'm missing something very obvious, but I can't seem to find an answer online.
In case you want to use a public variable it's best you declare it in a general module. You can, of course like you did, declare a public variable in a worksheet or workbook module as well but then you have to refer it explicitly.
In the case above you need to use ThisWorkbook.AllUpdated
But as always think and be careful , because as an example Prefix your variables with a data type indicator ... is not longer considered as good practise at least I consider that as wrong resp. bad practise.