英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论