Blank out field based on selected criteria

Paul15

New Member
Joined
Jun 25, 2020
Messages
27
Office Version
  1. 2019
Platform
  1. Windows
Hi all, hope you can help.

I have a user input form, on which there are two option buttons. On selecting either, I have the code to blank out certain other N/A input fields (turn red and N/A in the field). Speeds up data input by blanking out fields of no importance for the user. This works. Code below:

*******************************************************************
'Option button to mark out unwanted fields

Private Sub optRailPort_Click()

Me.cboVess.Text = ""
Me.cboVess.Enabled = False
Me.txtTMode.Text = ""
Me.txtTMode.Enabled = True

If cboVess.Enabled = False Then cboVess.BackColor = vbRed

' to place N/A in disabled field

If cboVess.Enabled = False Then cboVess.Text = "N/A"
If txtTMode.Enabled = True Then txtTMode.Text = "Rail"

'to clear other option from use

If optRailPort.Enabled = True Then
Me.optSeaPort.Visible = False

End If

End Sub

************************************************************************

I now wish to expand this idea where a user selects a particular entry in a Combo Box. There are two possibilities in the ComboBox, "Accompanied" and " Unaccompanied". Where "Unaccompanied" is selected I wish a user input text field, 'Nam2', to be blanked vbRed and have N/A as text entered in that field. Where "Accompanied" is selected I wish no action to be taken with the Nam2 field.

I have been playing with this code to try and get it to work:

'**************************************************
'to blank out Accompanied Adult if pax is unaccompanied

'Private Sub Accompanied_Change()

'Me.Accompanied.Text = "Unnaccompanied"
'Me.Nam2.Text = ""

'If Accompanied.Enabled = "Unnaccompanied" Then Nam2.BackColor = vbRed





'End Sub

'********************************************************

Any help please
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,850
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try following update to your code & see if does what you want

VBA Code:
Private Sub Accompanied_Change()

    With Me.Nam2
        .BackColor = IIf(Me.Accompanied.Text = "Unaccompanied", vbRed, vbWhite)
        .Locked = CBool(.BackColor = vbRed)
        .Text = IIf(.Locked, "N/A", "")
    End With

End Sub

Dave
 

Paul15

New Member
Joined
Jun 25, 2020
Messages
27
Office Version
  1. 2019
Platform
  1. Windows
Hi Dave,

Great start. Does what I want, bar one issue. The background colour of my user input fields is &H00C0FFFF&, a pale yellow. I do not want this to change on selection of the "Accompanied" option, things should remain the same as default. With your code it goes vbWhite. I have tried playing with your code but don't seem to be able to get that last bit to work.

Many thanks in advance and best wishes
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,850
Office Version
  1. 2019
Platform
  1. Windows
Try,

VBA Code:
Private Sub Accompanied_Change()

    With Me.Nam2
        .BackColor = IIf(Me.Accompanied.Text = "Unaccompanied", vbRed, &HC0FFFF)
        .Locked = CBool(.BackColor = vbRed)
        .Text = IIf(.Locked, "N/A", "")
    End With

End Sub

Dave
 
Solution

Paul15

New Member
Joined
Jun 25, 2020
Messages
27
Office Version
  1. 2019
Platform
  1. Windows
Awesome. nearly had it but left a & in. Many thanks. all sorted now

Regards
 

Forum statistics

Threads
1,144,370
Messages
5,723,960
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top