Excel VBA-使文本框输入为可选 [英] Excel VBA - Make Textbox input optional
问题描述
我的问题如下：
（1）
我有一个combobx CGselectionStrategies，应作为下面输入文本框的基础。启动用户窗体时，我希望它显示这些框的先前输入，具体取决于组合框的选择。
(1) I have a combobx "CGselectionStrategies" that should be the basis for the Input textboxes below. When the userform is started, I would like it to show the previous input for these boxes, depending on the Combobox selection.
使用以下代码将输入保存在工作表商品组中：
The Input is saved in the worksheet "Commodity Groups" with the following code:
Private Sub SaveCGStrategies_Click()
'Just general stuff
Dim outputBook As Workbook
Set outputBook = ActiveWorkbook
'Note-fields for PU Strategies, incl. Authors
Dim CGselectionStrategies As String
Dim NoteTargetMarket As String
Dim AuthorTargetMarket As String
Dim NotePUMStrategy As String
Dim AuthorPUMStrategy As String
Dim NotePUSStrategy As String
Dim AuthorPUSStrategy As String
Dim NotePULStrategy As String
Dim AuthorPULStrategy As String
CGselectionStrategies = Me.CGselectionStrategies
NoteTargetMarket = Me.NoteTargetMarket
AuthorTargetMarket = Me.NoteAuthorMarketInfo
NotePUMStrategy = Me.NotePUMStrat
AuthorPUMStrategy = Me.NoteAuthorPUMStratInfo
NotePUSStrategy = Me.NotePUSStrat
AuthorPUSStrategy = Me.NoteAuthorPUSStratInfo
NotePULStrategy = Me.NotePULStrat
AuthorPULStrategy = Me.NoteAuthorPULStratInfo
'Save CG Strategies behind them in the List on CG Worksheet
outputBook.Activate
outputBook.Worksheets("Commodity Groups").Select
With Me.CGselectionStrategies
If Me.CGselectionStrategies.value = "Halbzeuge (und Rohstoffe)" Then
Range("K2").Select
ActiveCell.value = NoteTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = AuthorTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUMStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUMStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUSStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUSStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePULStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPULStratInfo
End If
If Me.CGselectionStrategies.value = "Mechanische Konstruktionsteile" Then
Range("K62").Select
ActiveCell.value = NoteTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = AuthorTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUMStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUMStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUSStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUSStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePULStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPULStratInfo
End If
If Me.CGselectionStrategies.value = "Norm- und Katalogteile (ausser Elektro)" Then
Range("K87").Select
ActiveCell.value = NoteTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = AuthorTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUMStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUMStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUSStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUSStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePULStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPULStratInfo
End If
If Me.CGselectionStrategies.value = "Elektrische, elektronische und optische Komponenten und Baugruppen" Then
Range("K127").Select
ActiveCell.value = NoteTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = AuthorTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUMStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUMStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUSStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUSStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePULStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPULStratInfo
End If
If Me.CGselectionStrategies.value = "Hilfs-, Betriebs- und Produktionshifsmittel" Then
Range("K180").Select
ActiveCell.value = NoteTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = AuthorTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUMStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUMStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUSStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUSStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePULStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPULStratInfo
End If
If Me.CGselectionStrategies.value = "Subsysteme und Anlagen" Then
Range("K256").Select
ActiveCell.value = NoteTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = AuthorTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUMStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUMStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUSStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUSStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePULStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPULStratInfo
End If
If Me.CGselectionStrategies.value = "Handelsware" Then
Range("K299").Select
ActiveCell.value = NoteTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = AuthorTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUMStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUMStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUSStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUSStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePULStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPULStratInfo
End If
If Me.CGselectionStrategies.value = "Dienstleistungen" Then
Range("K310").Select
ActiveCell.value = NoteTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = AuthorTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUMStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUMStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUSStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUSStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePULStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPULStratInfo
End If
If Me.CGselectionStrategies.value = "Allgemeines und Administration" Then
Range("K360").Select
ActiveCell.value = NoteTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = AuthorTargetMarket
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUMStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUMStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePUSStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPUSStratInfo
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NotePULStrat
ActiveCell.Offset(0, 1).Select
ActiveCell.value = NoteAuthorPULStratInfo
End If
End With
End Sub
我的方法如下...
'Show old Strategies when selecting a combobox-item
'Start with short Text "Please choose a Commodity Group"
If Me.CGselectionStrategies.value = "" Then
Me.NoteTargetMarket.Text = CStr(ThisWorkbook.Sheets("Commodity Groups").Range("K445").value)
Me.Next Variable
Me.Next Variable
Me.Next Variable
End If
If Me.CGselectionStrategies.value = "Halbzeuge (und Rohstoffe)" Then
Me.NoteTargetMarket.Text = CStr(ThisWorkbook.Sheets("Commodity Groups").Range("K2").value)
Me.Next Variable
Me.Next Variable
Me.Next Variable
End If
...等等。不用说这是行不通的。我在网上找到了以下内容，并尝试使其适应我的最佳能力，但没有成功。
...and so on. Needless to say it does not work. I found the following online and tried to adapt it to the best of my abilities, but without success.
'Change Textbot Content based on Combobox selection
Dim wks As Excel.Worksheet
Dim selectedString As Variant
Dim row As Long
Dim value As Variant
Set wks = Worksheets("Commodity Groups")
If CGselectionStrategies.ListIndex <> -1 Then
selectedString = CGselectionStrategies.value
On Error Resume Next
row = Application.WorksheetFunction.Match(selectedString, wks.Columns(1), 0)
On Error GoTo 0
If row Then
value = wks.Cells(row, 2)
DomainOwnerTestBox.value = value
Else
'Value not found in the worksheet 'test'
End If
End If
End Sub
一个问题是不仅在第2列中存在多个输入值，而且这些输入值也被分隔其他许多行
我希望我的问题能以一种可以理解的方式得到解释。
One Problem was also that there are multiple Input values, not only in column 2, which are also separated by many other rows. I hope my poblem is explained in an understandable manner.
（2）
我的第二个问题简短得多，它是关于如何避免必须在用户表单中填写所有文本框的。一个问题是要填写200多个输入，每当我要测试即输入在数据库中的位置时，都会遇到运行时13错误类型不匹配。但是，如果我在每个框中都输入一个输入，它将顺利进行。这里是一段代码摘录，我是如何从userform输入中保存数据的：
(2) My second poblem, which is way shorter, is regarding how to avoid having to fill in all textboxes in a userform. The one is question has over 200 Inputs to fill out and whenever I want to test i.e. the positioning of the Input in the database, I get a runtime 13 mistake "Type mismatch." However, if I put an Input in every box, it runs through smoothly. Here a code excerpt how I save my data from the userform Input:
Dim Datum As Date
Dim SName As String
Dim PotentialS As String
Dim SuppNr As Long
Dim Active As String
Datum = Me.TextBox117
SName = Me.SuppName
PotentialS = Me.PotentialS
SuppNr = Me.SuppNo
Active = Me.Active
'Go to the first empty line on the output sheet (Meta DB) in this workbook
outputBook.Activate
outputBook.Worksheets("Meta DB").Range("A3").Select
If outputBook.Worksheets("Meta DB").Range("A3").Offset(1, 0) <> "" Then
outputBook.Worksheets("Meta DB").Range("A3").End(xlDown).Select
End If
'Go to A4 and from there always one below the last filled cell in A
ActiveCell.Offset(1, 0).Select
DatabaseRow = ActiveCell.row
'Post Values for new Entry
'Add a New Supplier Tab - Supplier Profile
ActiveCell.value = Datum
ActiveCell.Offset(0, 1).Select
ActiveCell.value = SName
ActiveCell.Offset(0, 1).Select
ActiveCell.value = PotentialS
ActiveCell.Offset(0, 1).Select
ActiveCell.value = SuppNr
ActiveCell.Offset(0, 1).Select
欢迎任何帮助和提示。
推荐答案
首先，我认为缩短 SaveCGStrategies_Click 代码将有助于更好地了解VBA。您要做的就是一步一步地检查每个选项以保存值，但是考虑到第一个选项已被选中，则您无需再检查其他选项本来会找到您的匹配项，每次也会重复复制该代码，以下检查选择并仅对相关单元格运行一次代码的单个实例。
Firstly I think shortening the SaveCGStrategies_Click code will help understand VBA a little better, what you have done is one by one check every option to save the values, but consider the first option was selected, then you would never need to check the others as you would have found your match, the code is also duplicated each time, the below checks selection and runs a single instance of the code once but against the relevant cells.
Private Sub SaveCGStrategies_Click()
Dim LngRow As Long
Dim outputBook As Workbook
Dim outputSheet As Worksheet
Set outputBook = ActiveWorkbook
Set outputSheet = outputBook.Worksheets("Commodity Groups")
'With Me.CGselectionStrategies
Select Case Me.CGselectionStrategies.Value
Case "Halbzeuge (und Rohstoffe)"
LngRow = 2
Case "Mechanische Konstruktionsteile"
LngRow = 62
Case "Norm- und Katalogteile (ausser Elektro)"
LngRow = 87
Case "Elektrische, elektronische und optische Komponenten und Baugruppen"
LngRow = 127
Case "Hilfs-, Betriebs- und Produktionshifsmittel"
LngRow = 180
Case "Subsysteme und Anlagen"
LngRow = 256
Case "Handelsware"
LngRow = 299
Case "Dienstleistungen"
LngRow = 310
Case "Allgemeines und Administration"
LngRow = 360
End Select
outputSheet.Cells(LngRow, 11) = Me.NoteTargetMarket
outputSheet.Cells(LngRow, 13) = Me.NoteAuthorMarketInfo
outputSheet.Cells(LngRow, 14) = Me.NotePUMStrat
outputSheet.Cells(LngRow, 15) = Me.NoteAuthorPUMStratInfo
outputSheet.Cells(LngRow, 16) = Me.NotePUSStrat
outputSheet.Cells(LngRow, 17) = Me.NoteAuthorPUSStratInfo
outputSheet.Cells(LngRow, 18) = Me.NotePULStrat
outputSheet.Cells(LngRow, 19) = Me.NoteAuthorPULStratInfo
Set outputSheet = Nothing
Set outputBook = Nothing
End Sub
以与您引用工作簿相同的方式，它还引用工作表，以使我们能够写入所需要的工作表范围nt用更少的代码。我没有使用过 .Select 和 Activate 函数，因为这些函数可能会出现性能问题。我还直接引用了这些值，而不是先将它们放在变量中，如果您打算在将它们写入单元格之前对其进行操作，那么变量可能会有用，但是如果它是从文本框到单元格的直接插入，我们可以直接通过。
In the same way you referenced the workbook, it also references the worksheet to enable us to write into the ranges of the worksheet that we want to with less code. I have not used the .Select and Activate functions that you had as these can have performance issues. I have also referenced the values directly and not placed them in a variable first, if you were planning to manipulate them prior to writing them to a cell then a variable may be of use but if it is a straight insert from textbox to cell, we can pass it straight through.
您的第二期需要更多的输入才能确定，但我怀疑与数据类型有关。
Your second issue needs more input to be certain but I suspect relates data types.
Dim Datum As Date
Datum = Me.TextBox117
Me.TextBox117 是否是有效日期格式的日期？可以如下检查：-
Is Me.TextBox117 a date in a valid date format? this could be checked as below: -
If IsDate(Me.TextBox117) then Datum = CDate(Me.TextBox117)
函数 CDate 确保将值传递给
Dim SuppNr As Long
SuppNr = Me.SuppNo
Me.SuppNo 是有效数字吗？可以如下检查：-
Is Me.SuppNo a valid number? this could be checked as below: -
If IsNumeric(Me.SuppNo) then SuppNr = CLng(Me.SuppNo)
我的建议是将其全部设置为 String 或照原样通过。
My recommendation would be while getting it work you set them all to String or pass them through as is.
这篇关于Excel VBA-使文本框输入为可选的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
