title: Excel VBA - Make Textbox input optional
body: <p>My Problem is the following:</p>
  
  <p><a href="https://i.stack.imgur.com/Nhev3.gif" rel="nofollow noreferrer">Userform visualization for understanding</a></p>
  
  <p>(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. </p>
  
  <p>The Input is saved in the worksheet "Commodity Groups" with the following code:</p>
  
  <pre><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
  </code></pre>
  
  <p>My Approach was the following...</p>
  
  <pre><code>'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
  </code></pre>
  
  <p>...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.</p>
  
  <pre><code>'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 &lt;&gt; -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
  </code></pre>
  
  <p>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. </p>
  
  <p>(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:</p>
  
  <pre><code>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) &lt;&gt; "" 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
  </code></pre>
  
  <p>Any help and tips are welcome.</p>