vba - 使用下拉列表中的值保存 Excel 工作簿(使用 VBA 代码)

Question

我是宏的新手，但是对它的工作原理有一些基本的了解，或者能够编写小的 VBA 代码。

Is it possible to add some predefined values inside a VBA code which can be used as a dropdown value and which can be used later, as per google there are two types of response like 'Yes we can use it' & some others says 'its impossible'.

我试图创建一个报告。我必须用一些值来保存它，比如

1. 预定义值，like("Analysis_Report"将是常量值)
2. DropDown 值(“Cluster1”、“Cluster2”、“Other”) - 无法获取
3. 日期和时间戳
4. 输入框(“用户名”)

但我无法将下拉菜单(尝试过 ComboBox)设置为我的宏。如果有人可以帮助我更正我的代码，那将非常感激。 :)

下面给出了我使用的一部分宏供您引用

Sub ImporttoNew_WorkBook_and_Close ()  
Dim DT As String  
Dim wbNam As String  
Dim Path  
Dim Cluster  
Dim UserName  

Workbooks.Add  
wbName = "Analysis_Report"

DT = Format(CStr(Now), "yyyy_mm_dd_hh_mm_ss") 

Path = InputBox("Enter Path ", "Enter value") & "\"  
UserName = "_" & InputBox("Type your Name", "Enter value")   
Cluster = ComboBox1.List = Array("Cluster1", "Cluster2", "Other") **'Not Working**  

ActiveWorkbook.SaveAs Filename:=Path & Cluster & wbName & DT & UserName   
ActiveWorkbook.close   
MsgBox "Document saved"   

End Sub  

提前致谢。 :)

Answer 1

组合框的问题在以下行:

Cluster = ComboBox1.List = Array("Cluster1", "Cluster2", "Other") **'Not Working** 

您必须先将集群设置为组合框，然后分配值:

Set Cluster = Tabelle1.ComboBox1  'change Tabelle1 to the Codename of the worksheet with your combobox1 in it
Cluster.List = Array("Cluster1", "Cluster2", "Other")