Public variable not passing to Worksheet_SelectionChange.

huangapple go评论69阅读模式
英文:

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

VBA最佳实践链接1

VBA最佳实践链接2

但一如既往,请慎重思考,因为例如“在变量前加上数据类型指示符…”不再被认为是良好的实践,至少我认为这是错误的或不良的实践。

英文:

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

VBA Best Practise Link 1

VBA Best Practise Link 2

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.

huangapple
  • 本文由 发表于 2023年5月13日 12:21:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/76241055.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定