Question regarding data validation

[slmthai]

Hello All,

I would like to get some help and suggestions regarding data validation. i am using data validation (list: DHL,UPS and Our Service) in Cell A25. i would like user when selecting "Our Service" from (drop down in A25) cell B25 will populated "our service" but when selecting other option B25 will be empty and user can enter their shipping account. A25 and B25 are required cells (can not be left empty) i have macro set to required to filling these cell.

thank you,
-Slm

 

[slmthai]

this post is disappearing fast, i really need this ASAP, i would appreciate if someone gives me a hit or if this not possible what other options.

Thank you very much in advance.

 

[slmthai]

no one? maybe it is very simple thing you guy do not want to answer but if it is give me a ref site or something to start with or mark the thread closed as it is not possible to do :P

 

[SuperFerret]

slmthai, what is the existing code you are using to make these fields mandatory?

(please ensure when posting to the board to use CODE TAGS)

Depending what you have already it may be possible to amend the macro to include a check whereas if A25 is Our Service then B25 will automatically complete with Our Service too

I'm no expert but I could certainly see if I can help

 

[slmthai]

Hi SuperFerret,

Here is the code macro to make these fields and others mandatory.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'user must enter data into all the defined cells before save
Dim test_rng As Range
Dim ret_str As String
Dim cell As Range
Set test_rng = ActiveSheet.Range("B12:B19,F12:F19,A23,B24,E23,F24") '**change range here****
For Each cell In test_rng
If cell.Value = "" Then
If ret_str = "" Then
ret_str = cell.Address
Else
ret_str = ret_str & " and " & cell.Address
End If
End If
Next
If ret_str <> "" Then
MsgBox "There is information missing in cell(s): " & ret_str & Chr(10) _
& Chr(10) & "You must fill in the cell(s) before you can save" _
, , "Missing Information"
Cancel = True
Else
End If
End Sub

 

[slmthai]

sorry pasted on ranges on the code, just ignore the previous code... here is the the correct one.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'user must enter data into all the defined cells before save
Dim test_rng As Range
Dim ret_str As String
Dim cell As Range
Set test_rng = ActiveSheet.Range("B12:B19,F12:F19,A25,B25,E25,F25") '**change range here****
For Each cell In test_rng
If cell.Value = "" Then
If ret_str = "" Then
ret_str = cell.Address
Else
ret_str = ret_str & " and " & cell.Address
End If
End If
Next
If ret_str <> "" Then
MsgBox "There is information missing in cell(s): " & ret_str & Chr(10) _
& Chr(10) & "You must fill in the cell(s) before you can save" _
, , "Missing Information"
Cancel = True
Else
End If
End Sub

 

[SuperFerret]

Hi slmthai, unfortunately we've gotten really busy so I won't have chance to look at this now. Hopefully this will bump it back up so someone with more time can have a look and help you out!

Regards,

SF

 

[slmthai]

Thank you for reply, i am trying to see if i can add If statement to the existing code.