Excel VBA - Make Textbox input optional

Question

My Problem is the following:

Userform visualization for understanding

(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

My Approach was the following...

'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

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) 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

Any help and tips are welcome.

So user selects from a combobox, enters some values in some textboxes, which then become the default values in said textboxes the next time the user makes the same combobox selection, is this correct? Don't know where you picked up the select-activecell offset habit from, but you could make your code a lot shorter and less error prone by referencing the cells directly. Also consider using `option explicit` at the top of your code, you're using undefined variables in there. Reference to point 2, you can't assign a variable a data type then set = null, check if it's null beforehand — Hello World, Jul 08 '16 at 15:03
Just want to say good on your for trying and providing us with how far you made it! Keep it up! — Gary Evans, Jul 08 '16 at 15:15
Ignoring your problem: You can use `With ActiveCell` & `End With` to save a lot of time writing your code. http://www.homeandlearn.org/with_end_with.html _Greetings to Germany, I guess :P_ — Benno Grimm, Jul 08 '16 at 15:43
First of all, thank you all very much for all your time! ______________________ To answer your question @Hello World, Yes, you are right, that is how it is supposed to work. ______________________ Gary Evans, thank you so much, I just feel bad for you having to read such bad beginners code again ______________________ Benno Grimm, I will definitely check out the tutorial! — InternInNeed, Jul 11 '16 at 05:44

score 0 · Answer 1 · edited May 23 '17 at 11:44

Hi one thing is for certain - you need to get rid of all of these .Select's

They make it incredibly hard to read. I myself have only just started learning VBA too (about 3 weeks ago). Please look at the following link - How to avoid using Select in Excel VBA macros . It should help you with your VBA readability. It will also make your code about a gazillion times faster.

This will also help you from having to use ActiveCell every other command.

Another tip is instead of you having

Dim *StringVariable* as string

as a line of code that you start off with at the beginning - if you want to name a cell something, just give its range and then make it = to "String" e.g.

Range("A1") = "This is a String"

I am not experienced enough with VBA to know what is the matter with your TextBox, but i hope this is a good start to aid your general VBA writing.

Thank you very much @lewisthegruffalo, I will have a look at the tutorial and will try to rework my code! — InternInNeed, Jul 11 '16 at 05:48

score 0 · Accepted Answer · answered Jul 08 '16 at 15:15

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

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

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)

The functionCDate ensure the value is passed into the variable as a date.

Dim SuppNr As Long
SuppNr = Me.SuppNo

Is Me.SuppNo a valid number? this could be checked as below: -

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

My recommendation would be while getting it work you set them all to String or pass them through as is.

your improved code works like a treat, thank you so so much haha I will read up on the "Case" function and try to implement it more. Unfortunately, I am under a lot of time pressure to get this and much more functionalities in the Excel tool working...and this is only the part where I have to save everything I am not sure if it is a matter of Data types because an error only occurs, if I leave the relevant TextBox Input empty. I will now try to replace everything with Strings and get back to you. Again, thank you for taking the time for such an extensive answer :) — InternInNeed, Jul 11 '16 at 06:05
Actually, is your code supposed to Show the previous selection as well? Because that still does not work. The idea is "user selects from a combobox, enters some values in some textboxes, which then become the default values in said textboxes the next time the user makes the same combobox selection." -- The user HelloWorld explained it so nicely in the first comments — InternInNeed, Jul 11 '16 at 06:17
I have managed to make it work with an IF function for every selection value, so never mind :) I also deleted the entire selection approach and replaced it by referencing to the cell ranges directly. — InternInNeed, Jul 11 '16 at 14:15
Much better to do by a reference than selection :) My code only saved the settings as that is all your code did. It does not load the settings on loading of the form as your code does not show that. I'm guessing my answer doesn't get marked as correct then :( and I do so love my [unicorn dollars](http://meta.stackoverflow.com/search?q=unicorn+dollars). — Gary Evans, Jul 11 '16 at 18:26
I didn't even know you could do that but it should be fixed now :) — InternInNeed, Jul 12 '16 at 05:29

Excel VBA - Make Textbox input optional

2 Answers2

Linked