Excel VBA-使“文本框”输入为可选

debugcn 发表于 Dev

实习生

我的问题如下：

（1）我有一个combobx“ CGselectionStrategies”，它应该是下面输入文本框的基础。启动用户窗体时，我希望它显示这些框的先前输入，具体取决于组合框的选择。

输入使用以下代码保存在工作表“商品组”中：

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

...等等。不用说这是行不通的。我在网上找到了以下内容，并尝试使其尽我所能进行调整，但没有成功。

'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列中有多个输入值，而且这些输入值也由许多其他行分隔。我希望我的问题能以一种可以理解的方式得到解释。

（2）我的第二个问题要简短一些，它是关于如何避免必须在用户表单中填写所有文本框的问题。一个问题是要填写200多个输入，每当我要测试（即输入在数据库中的位置）时，都会遇到运行时13错误“类型不匹配”。但是，如果我在每个框中都输入一个输入，它将顺利进行。这里是一段代码摘录，我是如何从用户表单输入中保存数据的：

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

欢迎任何帮助和提示。

加里·埃文斯（Gary Evans）

首先，我认为缩短SaveCGStrategies_Click代码将有助于更好地了解VBA，您要做的是一步一步检查每个选项以保存值，但考虑到选择了第一个选项，则您将不需要像检查其他选项那样进行检查。找到您的匹配项后，每次也会重复复制该代码，以下内容会检查选择并仅对相关单元格运行一次代码的单个实例。

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

用与您引用工作簿的方式相同，它也引用工作表，以使我们能够以较少的代码写入所需的工作表范围。我没有使用过您所拥有的.Select和Activate功能，因为这些可能会导致性能问题。我也直接引用了这些值，而不是先将它们放在变量中，如果您打算在将它们写入单元格之前对其进行操作，那么变量可能会有用，但是如果它是从文本框到单元格的直接插入，我们可以直接通过

您的第二个问题需要更多输入才能确定，但我怀疑与数据类型有关。

Dim Datum As Date
Datum = Me.TextBox117

Me.TextBox117日期是否为有效的日期格式？可以按以下方式进行检查：-

If IsDate(Me.TextBox117) then Datum = CDate(Me.TextBox117)

该函数CDate确保将值作为日期传递到变量中。

Dim SuppNr As Long
SuppNr = Me.SuppNo

是Me.SuppNo有效号码吗？可以按以下方式进行检查：-

If IsNumeric(Me.SuppNo) then SuppNr = CLng(Me.SuppNo)

我的建议是，在将其全部设置String为原样或通过它们的同时，使其生效。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-06-25

我来说两句

0条评论

登录后参与评论

来自分类Dev

Related 相关文章

文章

Excel VBA-使“文本框”输入为可选

Excel VBA-使“文本框”输入为可选

基于文本框输入的VBA Excel If语句

基于文本框输入的VBA Excel If语句

Excel VBA文本框时间验证为[h]：mm

文本框限制错误Excel VBA

VBA Excel-文本框验证

文本框日期格式Excel VBA

验证：在 VBA Excel 的文本框中只能输入一次特定的单词

VBA Excel 2010将文本框值设置为当前值

使用excel vba为动态构建的文本框名称分配值

出现msg框后清除VBA Excel文本框

用于填充组合框的 Excel VBA 文本框

Excel VBA麻烦填写Web文本框

Excel VBA：如何使旋转按钮控制多个文本框？

Excel VBA，空文本框和CInt

在图VBA Excel中的节点下方添加文本框

Excel VBA-检查文本框组

Excel VBA获取完整的文本框值

使用Excel VBA在文本框中创建公式

Excel VBA验证文本框中的特定格式

在VBA Excel中格式化文本框

验证用户表单上的文本框条目（Excel VBA）

Excel VBA在工作表上的用户窗体上填充文本框，具体取决于另一个文本框输入

Excel VBA 从 Excel 复制范围并将其粘贴到 Word 标题文本框

Excel VBA：根据文本框中设置的值在列表框中搜索值

Excel VBA：根据文本框中设置的值在列表框中搜索值

如何在Excel中使用VBA将文本插入（工作表）文本框？

VBA Excel：通过用户表单上的文本框指定导入文本文件的路径

Excel VBA周期为

多输入框Excel VBA