PDA

View Full Version : Solved: Country Name to stay fixed always in a cell



Pete
01-08-2009, 02:01 AM
Hi Experts

Need to fix the Country Name on the second page of form (Order form Front Page) so that the country name under "our customer details" and "Our Customer's Requirements" is always United Kingdom.

If the user tries to delete a message box appears saying "Are you sure you wish to change country field". But user can change the country name if they require.

Bob Phillips
01-08-2009, 02:24 AM
Unlock all cells except that one and protect the sheet.

georgiboy
01-08-2009, 03:06 AM
For this i would use a worksheet_change event. This code will go in the worksheet code window, not a standard module.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Ender

If Target.Row = 39 Or 58 Then
If Target.Column = 13 Then

If Target.Value <> "United Kingdom" Then

YesNo = MsgBox("Are you sure you wish to change country field?", vbYesNo + vbCritical, "Caution")

If YesNo = vbYes Then Exit Sub
If YesNo = vbNo Then Target.Value = "United Kingdom"

End If

End If
End If

Ender:
End Sub


Hope this helps

Pete
01-08-2009, 03:20 AM
thanks g-boy

works prefectly......excellent feedback. Sir