英文:
Public variable not passing to Worksheet_SelectionChange
问题
我是初学者,对VBA了解甚浅。我有一个用作数据库的Excel 2016电子表格。我已经声明了一个公共变量AllUpdated
,来定义电子表格中的数据是否是最新的。我有一个ActiveX标签,当数据不是最新时想显示它,当数据是最新时想让它消失。然而,无论用户输入如何,该变量似乎始终为False
,因为该标签始终可见。
我在打开工作簿时声明这个变量。用户会被提示是否要刷新数据(它与Access数据库连接)。如果他们选择“是”,变量应该变为“True”,如果选择“否”,变量将变为“False”。
Public AllUpdated As Boolean
Sub Workbook_Open()
cRefresh = MsgBox("您想刷新数据吗?", vbYesNo, "刷新")
If cRefresh = vbYes Then
Call sRefreshMaster
AllUpdated = True
Else
AllUpdated = False
End If
End Sub
当用户在工作簿中选择一个单元格时,如果数据过期,则标签应该显示;如果数据是最新的,则标签应该消失。
'- 只是一些我在网上找到的代码,用于在选择新单元格时在屏幕上显示和隐藏项目
'- 每次选择新单元格时
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
'- 如果数据是最新的,则隐藏水印
With MyPicture
.Visible = False
End With
Else
'- 如果数据已过时,则显示水印
'- 底部右侧单元格
With ActiveWindow.VisibleRange
r = .Rows.Count
c = .Columns.Count
Set BottomRightCell = .Cells(r, c)
End With
'- 定位图片
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
如果我手动更改标签为Visible = False
,则标签将消失,直到我点击新单元格,然后标签将重新出现。我该怎么解决这个问题?我认为我可能漏掉了非常明显的东西,但我似乎找不到在线答案。
英文:
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.
答案1
得分: 3
在需要使用公共变量时,最好在一个通用模块中声明它。当然,你也可以像你之前做的那样,在工作表或工作簿模块中声明公共变量,但那样你必须明确引用它。
在上面的情况下,你需要使用 ThisWorkbook.AllUpdated
。
但一如既往,请慎重思考,因为例如“在变量前加上数据类型指示符…”不再被认为是良好的实践,至少我认为这是错误的或不良的实践。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论