VBA – 通过动态创建的按钮访问工作表

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

VBA - Accessing Worksheets via Dynamically Created Buttons

问题

我正在尝试创建一个简单的程序,通过VBA将Excel中的信息汇总到一个用户友好的程序中。我在VBA方面是个初学者,因此在这个问题上需要您的帮助。

首先,一个工作簿由许多不同的工作表组成,每个工作表包含不同类别的信息。用户将点击一个按钮来选择哪个工作表的标题最适合他们的情况,然后程序将从该工作表中检索信息。我想要使程序能够在将来由经理根据需要自定义工作簿,包括更改工作表的数量,程序将相应地做出响应。

因此,我决定在用户窗体上动态创建按钮。我的当前模块中的代码如下:

Sub Layer1()
    
    Dim ws As Worksheet
    Dim maincats() As Variant
    Dim i As Long
    
    ReDim maincats(ThisWorkbook.Sheets.Count)
    
    For Each ws In ThisWorkbook.Sheets
        maincats(i) = ws.Range("A1").Value
        i = i + 1
    Next ws
    
    Dim ub As Integer
    Dim text As String
    ub = UBound(maincats) - 1   
    
    Dim x As Integer
    Dim btn As CommandButton
    Dim topPos As Integer
    
    topPos = 100
    
    For x = 0 To ub
        Set btn = UserForm1.Controls.Add("Forms.CommandButton.1")
        With btn
            .Caption = maincats(x)
            .Left = 50
            .Top = topPos
            .Width = 100
            .Height = 30
        End With
        topPos = topPos + 40
    Next x
    
    UserForm1.Show
    
End Sub

上述代码似乎适合我的基本需求,它创建了一个带有与工作表数量相对应的正确数量的按钮的用户窗体。现在我需要实际将按钮链接到相应的工作表,那么在这种情况下我可以做些什么呢?我已经试图在网上查找信息,但大多数指南似乎是关于如何在用户窗体中已经创建了按钮的情况,而在我的情况下似乎不适用。

请问有没有人能够提供在这种情况下的帮助?如果需要更多的解释,请随时告诉我!我会非常感激任何帮助。

提前谢谢!

英文:

I am attempting to create a simple programme to consolidate information on Excel via VBA into a user-friendly programme. I am a beginner in VBA, hence do require your kind help in this matter.

First of all, a workbook is comprised of many different worksheets, each one containing different categories of information. The user will click on a button to select which worksheet title is most suitable for their circumstance, before the programme retrieves information within that worksheet. I want to make the programme such that the manager in future can continue to customise the workbook in future by altering the number of worksheets and the programme would respond accordingly.

I have thus decided that I need dynamically created buttons on a userform. My current code in a module goes:

Sub Layer1()

Dim ws As Worksheet
Dim maincats() As Variant
Dim i As Long

ReDim maincats(ThisWorkbook.Sheets.Count)

For Each ws In ThisWorkbook.Sheets
    maincats(i) = ws.Range("A1").Value
    i = i + 1
Next ws


Dim ub, As Integer
Dim text As String
ub = UBound(maincats) - 1   


Dim x As Integer
Dim btn As CommandButton
Dim topPos As Integer


topPos = 100

For x = 0 To ub
    Set btn = UserForm1.Controls.Add("Forms.CommandButton.1")
    With btn
        .Caption = maincats(x)
        .Left = 50
        .Top = topPos
        .Width = 100
        .Height = 30
    End With
    topPos = topPos + 40

Next x

UserForm1.Show

End Sub

The above code appears to suit my elementary needs decently, having creating a userform with the correct number of buttons that correspond to the correct number of worksheets. Now I need to actually link the buttons to the corresponding worksheets, so what can I do in this case? I have attempted to comb through the web for information, but most guides appear to clarify on doing so with buttons already created in the UserForm, which I believe in my case does not apply.

May I know if anyone has any help to offer in this case? Feel free to let me know if you need more clarifications from me! Any help will be greatly appreciated.

Thank you in advance!

答案1

得分: 2

Sure, here's the translated code portion:

由于您正在浏览工作表,您可以使用`ws.Name`而不是`ws.Range("A1").Value`。这样,即使用户更改工作表名称,您动态创建的按钮也将引用正确的工作表。我一直在尝试使这个工作一段时间,使用了[Brett的答案](https://stackoverflow.com/a/10226842/19353309),但只有使用[Darren的答案](https://stackoverflow.com/a/48384652/19353309) 我才能让您的代码工作。

就像他的答案一样,您需要在其中添加一个类模块:

Public WithEvents MyButton As MSForms.CommandButton

Private Sub MyButton_Click()
    ThisWorkbook.Worksheets(MyButton.Caption).Activate
    UserForm1.Hide
End Sub

并在您的用户窗体的代码中:

Public MyEvents As New Collection

Private Sub UserForm_Initialize()

    Dim tmpCtrl As Control
    Dim CmbEvent As clsMyEvents
    Dim ws As Worksheet
    Dim maincats() As Variant
    Dim i As Long
    ReDim maincats(ActiveWorkbook.Sheets.Count)

    For Each ws In ActiveWorkbook.Sheets
        maincats(i) = ws.Name
        i = i + 1
    Next ws

    Dim ub As Integer
    Dim text As String
    ub = UBound(maincats) - 1

    Dim x As Integer
    Dim btn As Object
    Dim topPos As Integer

    topPos = 100

    For x = 0 To ub
        Set btn = UserForm1.Controls.Add("Forms.CommandButton.1")
        With btn
            .Caption = maincats(x)
            .Left = 50
            .Top = topPos
            .Width = 100
            .Height = 30
        End With
        topPos = topPos + 40

        Set CmbEvent = New clsMyEvents
        Set CmbEvent.MyButton = btn
        MyEvents.Add CmbEvent
    Next x

End Sub

然后您可以在ThisWorkbook的`Workbook_Open`中使用`UserForm1.Show`:

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

或者只需将其添加到按钮或您希望用户再次查看用户窗体的方式。

Please note that the translated code assumes you are familiar with VBA programming.

英文:

Since you're going through the sheets, you can use ws.Name instead of ws.Range("A1").Value. That way, even if a user changes the sheet name, your dynamically created button will refer to the correct sheet. Been trying to make this work for a bit now with Brett's answer but it was only with Darren's answer that I got your code to work.

Just like in his answer, you'll have to add a class module with in it:

Public WithEvents MyButton As MSForms.CommandButton

Private Sub MyButton_Click()
    ThisWorkbook.Worksheets(MyButton.Caption).Activate
    UserForm1.Hide
End Sub

and in your userform's code:

Public MyEvents As New Collection

Private Sub UserForm_Initialize()

    Dim tmpCtrl As Control
    Dim CmbEvent As clsMyEvents
    Dim ws As Worksheet
    Dim maincats() As Variant
    Dim i As Long
    ReDim maincats(ActiveWorkbook.Sheets.Count)
    
    For Each ws In ActiveWorkbook.Sheets
        maincats(i) = ws.Name
        i = i + 1
    Next ws
    
    
    Dim ub As Integer
    Dim text As String
    ub = UBound(maincats) - 1
    
    
    Dim x As Integer
    Dim btn As Object
    Dim topPos As Integer
    
    topPos = 100
    
    For x = 0 To ub
        Set btn = UserForm1.Controls.Add("Forms.CommandButton.1")
        With btn
            .Caption = maincats(x)
            .Left = 50
            .Top = topPos
            .Width = 100
            .Height = 30
        End With
        topPos = topPos + 40
        
        Set CmbEvent = New clsMyEvents
        Set CmbEvent.MyButton = btn
        MyEvents.Add CmbEvent
    Next x
    
End Sub

And then you can use UserForm1.Show at Workbook_Open in ThisWorkbook:

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

or just add it to a button or however you want the users to get the opportunity to see the userform again.

Would've loved being able to use Brett's answer as it seems easier to use (imho) but not going to keep staring blind at it ^^

答案2

得分: 2

以下是翻译好的部分:

这将帮助你入门:

创建一个名为 `myButton` 的类模块。将以下代码添加到模块中:

    Public WithEvents mBtn As MSForms.CommandButton
    
    Public Sub init(ByVal caption As String, top As Integer)
        mBtn.Caption = caption
        mBtn.Top = top
    End Sub
    
    Private Sub mBtn_Click()
        Sheets(mBtn.Caption).Select
    End Sub
    
    Private Sub Class_Initialize()
        Set mBtn = UserForm1.Controls.Add("Forms.CommandButton.1")
        With mBtn
            .Left = 50
            .Width = 100
            .Height = 30
        End With
    End Sub

然后按照以下方式编辑你的代码:

    Sub Layer1()
        Dim ws As Worksheet
        Dim i As Long
        
        Dim btn As myButton
        Dim btns() As myButton
        
        Dim topPos As Integer
        
        ReDim btns(ThisWorkbook.Sheets.Count)
        
        topPos = 100
        For Each ws In ThisWorkbook.Sheets
            Set btn = New myButton
            Set btns(i) = btn
            With btns(i)
                .init ws.Name, topPos
            End With
            topPos = topPos + 40
            i = i + 1
        Next ws
        
        UserForm1.Show
    End Sub

我稍微调整了你的代码。我使用 `ws.Name` 动态获取工作表名称,而不是读取单元格 A1。这也消除了需要存储工作表名称的数组。
英文:

This should get you started:

Create a class module myButton. Add this code to the module:

Public WithEvents mBtn As MSForms.CommandButton

Public Sub init(ByVal caption As String, top As Integer)
    mBtn.caption = caption
    mBtn.top = top
End Sub

Private Sub mBtn_Click()
    Sheets(mBtn.caption).Select
End Sub

Private Sub Class_Initialize()
    Set mBtn = UserForm1.Controls.Add("Forms.CommandButton.1")
    With mBtn
        .Left = 50
        .Width = 100
        .Height = 30
    End With
End Sub

And edit your code like this:

Sub Layer1()
    Dim ws As Worksheet
    Dim i As Long
    
    Dim btn As myButton
    Dim btns() As myButton
    
    Dim topPos As Integer
    
    ReDim btns(ThisWorkbook.Sheets.Count)
    
    topPos = 100
    For Each ws In ThisWorkbook.Sheets
        Set btn = New myButton
        Set btns(i) = btn
        With btns(i)
            .init ws.Name, topPos
        End With
        topPos = topPos + 40
        i = i + 1
    Next ws
    
    UserForm1.Show
End Sub

I adjusted your code slightly. I grab the Worksheet names dynamically with ws.Name instead of reading cell A1. This also eliminates the need for an array that hold the worksheet names.

huangapple
  • 本文由 发表于 2023年5月24日 17:32:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/76322056.html
匿名

发表评论

匿名网友

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

确定