Field selection and blan

Paul15

New Member
Joined
Jun 25, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
Dear forum,

i have the below code working on a user form whereby it blanks out certain fields when, in this instance "Paris" is selected. it works fine, however i wish to extend the functionality to work when other places are selected in the same way. ie if "Lille" were selected from the same me.port field instead of "Paris" and so on for a number of selections.

I have tried copying down for the other locations in the Private sub but this does not work.

should there be an If else function or something

Regards in advance

Paul

VBA Code:
Private Sub Port_Change()

'blanks out vehicle fields for train ports

'Paris

    With Me.nam3
        .BackColor = IIf(Me.Port.Text = "Paris", vbRed, &HC0FFFF)
        .Locked = CBool(.BackColor = vbRed)
        .Text = IIf(.Locked, "N/A", "")
    End With
   
    With Me.Add
        .BackColor = IIf(Me.Port.Text = "Paris", vbRed, &HC0FFFF)
        .Locked = CBool(.BackColor = vbRed)
        .Text = IIf(.Locked, "N/A", "")
    End With
       
    With Me.Veh
        .BackColor = IIf(Me.Port.Text = "Paris", vbRed, &HC0FFFF)
        .Locked = CBool(.BackColor = vbRed)
        .Text = IIf(.Locked, "N/A", "")
    End With
   
    With Me.VRN
        .BackColor = IIf(Me.Port.Text = "Paris", vbRed, &HC0FFFF)
        .Locked = CBool(.BackColor = vbRed)
        .Text = IIf(.Locked, "N/A", "")
    End With
End Sub
 
Last edited by a moderator:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

dmt32

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


Rich (BB code):
Private Sub Port_Change()
    Dim IsLocked           As Boolean
    Dim Destination       As Variant

    Destination = Array("Paris", "Lille")

    IsLocked = Not IsError(Application.Match(Me.Port.Text, Destination, 0))

    'blanks out vehicle fields for train ports

    With Me.nam3
        .Locked = IsLocked
        .BackColor = IIf(.Locked, vbRed, &HC0FFFF)
        .Text = IIf(.Locked, "N/A", "")
    End With
 
    With Me.add
        .Locked = IsLocked
        .BackColor = IIf(.Locked, vbRed, &HC0FFFF)
        .Text = IIf(.Locked, "N/A", "")
    End With
     
    With Me.veh
        .Locked = IsLocked
        .BackColor = IIf(.Locked, vbRed, &HC0FFFF)
        .Text = IIf(.Locked, "N/A", "")
    End With
 
    With Me.vrn
        .Locked = IsLocked
        .BackColor = IIf(.Locked, vbRed, &HC0FFFF)
        .Text = IIf(.Locked, "N/A", "")
    End With
End Sub

Add to the array shown in BOLD, all other places you want code to apply to.

Dave
 
Solution

Paul15

New Member
Joined
Jun 25, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
Dave,
brilliant thank you, exactly what I was after.

Regards
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows
Dave,
brilliant thank you, exactly what I was after.

Regards

Welcome glad update does what you want

As an aside & if want to shorten code a little could try this update

VBA Code:
Private Sub Port_Change()
    Dim IsLocked        As Boolean
    Dim Destination     As Variant, ctrl As Variant

    Destination = Array("Paris", "Lille")

    IsLocked = Not IsError(Application.Match(Me.Port.Text, Destination, 0))

    'blanks out vehicle fields for train ports
    
    For Each ctrl In Array(Me.nam3, Me.add, Me.veh, Me.vrn)
        With ctrl
        .Locked = IsLocked
        .BackColor = IIf(.Locked, vbRed, &HC0FFFF)
        .Text = IIf(.Locked, "N/A", "")
        End With
    Next ctrl
      
End Sub

Dave
 

Forum statistics

Threads
1,148,193
Messages
5,745,272
Members
423,941
Latest member
CluelessAboutExcel

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