Field selection and blan

[Paul15]

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

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

 

[dmt32]

Hi,
try this update to your code & see if does what you want

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

 

[Paul15]

Dave,
brilliant thank you, exactly what I was after.

Regards

 

[dmt32]

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

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