☰

VBA 代码帮助“转到并选择”替代方案

tag-icon • tag-icon microsoft-excel vba
VBA 代码帮助“转到并选择”替代方案

这是我第一次尝试编写一些 VBA 代码的工作簿，虽然我可以使工作簿正常运行，但由于函数的原因，我无法隐藏“面板计算”表goto and select。

有没有人有其他方法来获得这个结果而不使用这些语句？

Private Sub Add_Click()

Dim dis As String
Dim mat As String
Dim size As String
Dim qty As String
Dim cst As String
Dim hls As String
Dim bnd As String
Dim sht As String

mat = PanelMatOutput.Value
size = PanelSizeOutput.Value
qty = PanelQuantity.Value
cst = PanelCost.Value
hls = PanelHoles.Value
bnd = PanelBends.Value
sht = Sheettally.Value

Application.Goto (ActiveWorkbook.Sheets("Panel Calculations").Range("a1"))
Worksheets("Panel Calculations").Range("A" & Rows.Count).End(xlUp).Offset(1).Select

ActiveCell.Value = qty
    ActiveCell.Offset(0, 1).Select
    
ActiveCell.Value = hls
    ActiveCell.Offset(0, 1).Select
    
ActiveCell.Value = bnd
    ActiveCell.Offset(0, 1).Select

ActiveCell.Value = mat
    ActiveCell.Offset(0, 1).Select
    
ActiveCell.Value = size
    ActiveCell.Offset(0, 1).Select

ActiveCell.Value = cst
    ActiveCell.Offset(0, 1).Select
    
ActiveCell.Value = sht
Application.Goto (ActiveWorkbook.Sheets("Dashboard").Range("h5"))

End Sub

答案1

正如您所知，您无法通过隐藏工作表的宏调用工作表数据。

我建议如下（伪代码，您可能需要稍微调整一下）

Private Sub Add_Click()

' dims up here

' set variables here

'Prevent the screen from updating
Application.ScreenUpdating = False

'Unhide the sheet
Sheets("panel calculations").Visible = True

Application.Goto (ActiveWorkbook.Sheets("Panel Calculations").Range("a1"))
Worksheets("Panel Calculations").Range("A" & Rows.Count).End(xlUp).Offset(1).Select

' your active cell bits

' rehide the sheet
Sheets("panel calculations").Visible = False

'permit screen updates again
Application.ScreenUpdating = True

ActiveCell.Value = sht
Application.Goto (ActiveWorkbook.Sheets("Dashboard").Range("h5"))

End Sub

这将取消隐藏您的工作表，允许更新在后台进行，然后在完成后重新隐藏工作表。

答案2

无需取消隐藏/选择任何内容。这将放置值并使选定的单元格保持代码运行之前的状态。

注意 - 我使用的是ThisWorkbook而不是ActiveWorkbook。
ThisWorkbook是代码所在的文件，
ActiveWorkbook是当前活动的文件 - 用户可以在代码运行时更改它。

Private Sub Add_Click()

    Dim dis As String
    Dim mat As String
    Dim size As String
    Dim qty As String
    Dim cst As String
    Dim hls As String
    Dim bnd As String
    Dim sht As String
    Dim LastRow As Long
    
    mat = PanelMatOutput.Value
    size = PanelSizeOutput.Value
    qty = PanelQuantity.Value
    cst = PanelCost.Value
    hls = PanelHoles.Value
    bnd = PanelBends.Value
    sht = Sheettally.Value
    
    'Anything starting with "." between With and End With
    'is looking at "Panel Calculations"
    With ThisWorkbook.Worksheets("Panel Calculations")
    
        'Find the last row number in column A.
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        
        'Put in the last row in column A (1), B (2), etc.
        .Cells(LastRow, 1) = qty
        .Cells(LastRow, 2) = hls
        .Cells(LastRow, 3) = bnd
        .Cells(LastRow, 4) = mat
        .Cells(LastRow, 5) = size
        .Cells(LastRow, 6) = cst
        .Cells(LastRow, 7) = sht
    End With

End Sub

相关内容