# Specific value is entered on userform unless user overides



## ipbr21054 (Jan 2, 2023)

Hi,
Im working on a userform where the user will enter 6 separate values in 6 separate textboxes followed by a letter which will be entered in textbox7
Currently Textbox 7 value is taken from the user selected option button1 "G" or option button2 "M"

Now 99.9% of the time this value will be G
So im trying to limit the amount of unnecessary clicks on the task for the user & to always have textbox7 value set at G unless the user overides it for M

Any advice for how to get this working.
The lines of code below are what i am using at present to put a value in textbox7
Once a value is entered & after a 2 second delay the userform values are pasted to my worksheet.

It can be as basic as having G as textbox 7 default UNLESS option button M is selected within x amount of seconds to then override textbox7 value otherwise just continue with the default

Thanks


```
Private Sub TextBox7_Change()
    TextBox7 = UCase(TextBox7)
   If TextBox7.Value <> 0 Then
   Application.Wait (Now + TimeValue("0:00:02")) ' DELAY BEFORE CODE IS TRANSFERED
    Call DISCOCODE
   End If
End Sub
Private Sub OptionButton1_Click() ' UK PCB REMOTE
If OptionButton1.Value = True Then
  TextBox7.Visible = True
  TextBox7.Value = "G"
End If
End Sub
Private Sub OptionButton2_Click() ' UK SUSPENSION REMOTE
If OptionButton2.Value = True Then
  TextBox7.Visible = True
  TextBox7.Value = "M"
End If
End Sub
```


----------



## HaHoBe (Jan 2, 2023)

Hi ipbr21054,

I would use the Initialize or Activate Event of the UserForm and use a code like this behind the UserForm


```
Dim mblnLoad As Boolean
'
Private Sub TextBox7_Change()
  If mblnLoad Then
    TextBox7 = UCase(TextBox7)
    If TextBox7.Value <> 0 Then
    Application.Wait (Now + TimeValue("0:00:02")) ' DELAY BEFORE CODE IS TRANSFERED
    Call DISCOCODE
    End If
  End If
End Sub
Private Sub OptionButton1_Click() ' UK PCB REMOTE
If OptionButton1.Value = True Then
  With TextBox7
    .Enabled = True
    .Value = "G"
  End With
End If
End Sub
Private Sub OptionButton2_Click() ' UK SUSPENSION REMOTE
If OptionButton2.Value = True Then
  With TextBox7
    .Enabled = True
    .Value = "G"
  End With
End If
End Sub

Private Sub UserForm_Initialize()
mblnLoad = False
With TextBox7
  .Value = "G"
  .Enabled = False
End With
mblnLoad = True
End Sub
```

I'm not so sure what the transferring code will do as when no change is made with the OptionsButtons your procedure will not be called (maybe add that line to a button?)

Holger


----------



## ipbr21054 (Jan 2, 2023)

Hi,
Just confirming there is a typo ?
OptionButton1 & 2 you have the value G twice.

Ive Changed to OptionButton1 = G OptionButton2 = M

I enter the 6 values to the userform & wait.
Nothing happens.
If i select OptionButton2 then the code continues.

My issue is with nothing happens if i dont do anything.

Can we do this somehow.
Have Textbox7 default value set at G
If withing 5 second delay OptionButton2 is not selected then contine.

If OptionButton2 is selected with 5 second delay then Textbox7 change G to M then continue

Thanks


----------



## ipbr21054 (Jan 2, 2023)

This code here.
Edit it so if OptionButton2 is not selected within 5 seconds then continue.
Otherwise when the user selects it then code runs fine.

So the issue it nothing happens currently if OptionButton2 isnt selected



```
Private Sub TextBox7_Change()
  If mblnLoad Then
    TextBox7 = UCase(TextBox7)
    If TextBox7.Value <> 0 Then
    Application.Wait (Now + TimeValue("0:00:02")) ' DELAY BEFORE CODE IS TRANSFERED
    Call DISCOCODE
    End If
  End If
End Sub
```


----------



## HaHoBe (Jan 2, 2023)

Hi,

sorry for the typos - my bad. But I'm having problems understanding why you want a timelimit for any change in TextBox7. Please understand that just from seeing part of the code the logic for this is not clear for me. From what I guess by now you should make the order in which the TextBoxes are activated from 1 to 7. At the end of TextBox6 I would enable TextBox7 for entering but omitt any change or time limit - I would use a button to check if all Textboxes are filled accordingly and then proceed.

Holger


----------



## ipbr21054 (Jan 2, 2023)

The issue is the form does not paste the values to the worksheet because Textbox7 is awaiting a change in value.
If i select OptionButton2 then the change from G to M is made then the code continues.

So if i need G to be used then there is no change so the code does not continue


----------



## HaHoBe (Jan 2, 2023)

Hi,

again: I have trouble understanding the logic from your code (that's me, believe me). Whenever I use a Form with Textboxes I will check (either with a CommandButton or any change within a ListBox/ComboBox which will only be available when all other six textboxes have been filled) and proceed. At what point in your code will the optionsbuttions be activated? If they are available on startup how do you take care that all other six textboxes have been filled before?

Ciao,
Holger


----------



## ipbr21054 (Jan 2, 2023)

The userform opens.
Only Textbox1 is shown.
A value is entered & now TextBox2 is shown.

This continues so all Textboxes have a value.

Your code works fine BUT i need the code to run when there is no change in TextBox7 BECAUSE THE DEFAULT IS G


----------



## HaHoBe (Jan 2, 2023)

Maybe a slightly altered way may help here. On Calling/Activating Textbox7 is disabled. Code for Textbox6 is altered to call an Inputbox which is modal and not due to any timer:


```
Private Sub UserForm_Activate()
TextBox7.Enabled = False
End Sub

Private Sub TextBox6_Change()
Dim varRet As Variant
If Len(Me.TextBox6.Value) > 0 Then
DIA:
  varRet = Application.InputBox("Hit Enter or insert 1 for 'UK PCB REMOTE' or enter 2 for 'UK SUSPENSION REMOTE'", "Your Choice", 1, Type:=2)
  If Not IsNumeric(varRet) Or varRet > 2 Then GoTo DIA
  If varRet = 0 Then Exit Sub
  With TextBox7
    .Enabled = True
    .Value = IIf(varRet = 1, "G", "M")
  End With
  Call DISCOCODE
End If
End Sub
```

I already stated that I'm no fan of giving the user a short timed choice to continue (which the user might miss) and proceed - you may enter anything into the InputBox but the relevant vaues are 0 (Cancel), 1 and 2 like stated. And the user must only hit enter to proceed and have M entered into TextBox7.

I will continue to think about a way to integrate the timer into OptionsButtons.

Holger


----------



## ipbr21054 (Jan 2, 2023)

There is a slim possibilty of missing it.
The value G is 99.9% going to be used so all is good.

The .01% of which is M works when option button is selected.

So 99.9% of the time all is good BUT as it is a change event & G is default then the code doesnt run.
What else can be used in place of the change event as if G is correct & it will be 99.9% of the time then the code needs to just continue

Job then complete,the change event is stopping this


----------



## ipbr21054 (Jan 2, 2023)

Hi,
Im working on a userform where the user will enter 6 separate values in 6 separate textboxes followed by a letter which will be entered in textbox7
Currently Textbox 7 value is taken from the user selected option button1 "G" or option button2 "M"

Now 99.9% of the time this value will be G
So im trying to limit the amount of unnecessary clicks on the task for the user & to always have textbox7 value set at G unless the user overides it for M

Any advice for how to get this working.
The lines of code below are what i am using at present to put a value in textbox7
Once a value is entered & after a 2 second delay the userform values are pasted to my worksheet.

It can be as basic as having G as textbox 7 default UNLESS option button M is selected within x amount of seconds to then override textbox7 value otherwise just continue with the default

Thanks


```
Private Sub TextBox7_Change()
    TextBox7 = UCase(TextBox7)
   If TextBox7.Value <> 0 Then
   Application.Wait (Now + TimeValue("0:00:02")) ' DELAY BEFORE CODE IS TRANSFERED
    Call DISCOCODE
   End If
End Sub
Private Sub OptionButton1_Click() ' UK PCB REMOTE
If OptionButton1.Value = True Then
  TextBox7.Visible = True
  TextBox7.Value = "G"
End If
End Sub
Private Sub OptionButton2_Click() ' UK SUSPENSION REMOTE
If OptionButton2.Value = True Then
  TextBox7.Visible = True
  TextBox7.Value = "M"
End If
End Sub
```


----------



## HaHoBe (Jan 2, 2023)

Hi,

I tried buidling a system according to the code you supplied. At the end of Textbox6_Change I wrote "G" to TextBox7. For me there is a logical error in your code: you set up the procedure to check for the contents of TextBox7 and run code. As long as that procedure will run no change to TextBox7 can be made so checking for somebody to press OptionButton2 will not be monitored, the procedure will finish with whatever was entered to start the procedure. 

I know my suggestion about using the Inputbox at the end of TB6 isn't the smartest one but it will allow the user to take action and enter different values. Using a Listbox on the UserForm instead of the Optionbuttons might be an alternative but will need a click from the user.

Holger


----------



## HaHoBe (Jan 2, 2023)

Hi,

maybe this could be a way around. Instead of holding the Application through _Wait_ use _OnTime_ to start the checking macro after a given time. Problem might be to find the proper event to start the macro (I used TextBox6_*Exit* for this sample).

Code behind the userform (mine is named UserForm4):


```
Private Sub OptionButton1_Click() ' UK PCB REMOTE
If OptionButton1.Value = True Then
  TextBox7.Value = "G"
End If
End Sub
Private Sub OptionButton2_Click() ' UK SUSPENSION REMOTE
If OptionButton2.Value = True Then
  TextBox7.Value = "M"
End If
End Sub

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'on leaving TextBix6 "G" is inserted in TextBox7, a global variable
'holding the present value is filled and macro is started
If Len(Me.TextBox6.Value) > 0 Then
  g_varData = "G"
  TextBox7.Value = "G"
  Check_Me
End If
End Sub
```

In a standard module:


```
Public g_varData As Variant

Sub Check_Me()
Application.OnTime Now + TimeValue("00:00:05"), "my_Procedure"
End Sub

Sub my_Procedure()
If UserForm4.TextBox7.Value = g_varData Then
  Call DISCOCODE
Else
  g_varData = UserForm4.TextBox7.Value
  Application.OnTime Now + TimeValue("00:00:05"), "my_Procedure"
End If
'/// added to unload the UserForm
Unload UserForm4
End Sub

Sub DISCOCODE()
  '/// put your code here
  '/// for control I just print content of TB7
  Debug.Print UserForm4.TextBox7.Value
End Sub
```

HTH
Holger


----------



## ipbr21054 (Jan 3, 2023)

Thanks for your time but that stil soesnt allow the user to select the M option !% for it to work.

I think i will just have to leave it as it is.


----------



## HaHoBe (Jan 3, 2023)

Hi ipbr21054,

be assured that I tested the code before posting it here. I added 


```
Private Sub TextBox7_Change()
Debug.Print Now
Debug.Print TextBox7.Value
End Sub
```

and found


```
03.01.2023 13:21:49 
G
03.01.2023 13:21:51 
M
```

in the Immediate Window which correspond to the view of change in TextBox7 while the code is running.

You can find the workbook for download to test here.

Holger


----------



## ipbr21054 (Jan 3, 2023)

I see no option to download only view but then i only see a blank worksheet


----------



## HaHoBe (Jan 3, 2023)

Hi,

I see the menu in german (the english commands may differ as I translated them):


Datei - File
Seichern Unter - SaveAs
Eine Kopie herunterladen - Download a Copy
and download should start to the folder specified in your browser.

Holger


----------

