时间戳

首页 > 解决方案 > 如何使用户窗体将数据添加到工作簿中所有可见工作表中的相同单元格

excel - 如何使用户窗体将数据添加到工作簿中所有可见工作表中的相同单元格

问题描述

如何让我的用户窗体将数据添加到工作簿中所有可见工作表中的相同单元格。另外，如何创建一个用户表单按钮来从我的工作簿中的所有可见工作表中删除所有这些数据。

    Private Sub CommandButton4_Click()
Dim ws As Worksheet

Set ws = ActiveSheet

ws.Unprotect Password:="Tech1987!"

With ws
    .Range("C5") = CDbl(Me.tbRev)
    .Range("C6") = CDbl(Me.tbCost)
End With

Me.tbRev.Value = ""
Me.tbCost.Value = ""

ws.Protect Password:="Tech1987!"

End Sub

标签： excelvba

解决方案

循环工作表

Option Explicit

Private Sub CommandButton4_Click()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        With ws
            If .Visible = xlSheetVisible Then
                .Unprotect Password:="Tech1987!"
                .Range("C5") = CDbl(Me.tbRev)
                .Range("C6") = CDbl(Me.tbCost)
                .Protect Password:="Tech1987!"
            End If
        End With
    Next ws
    
    Me.tbRev.Value = ""
    Me.tbCost.Value = ""

End Sub

Private Sub CommandButton5_Click()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        With ws
            If .Visible = xlSheetVisible Then
                .Unprotect Password:="Tech1987!"
                .Range("C5:C6").Value = Empty
                .Protect Password:="Tech1987!"
            End If
        End With
    Next ws

End Sub

推荐阅读