E
edentonmomster
I have an excel template that I have set up to use as an order form.
need to be able to do the following
1) one field is a drop down value list - I need the user to select on
of the values - I found this thread and tried it - and it almost worke
but I'm caught in a loop! I found this very helpful response to
similar question and tried it
Default Require specific cell entry before saving fil
Try this VBA event code (change the sheet name as appropriate)
-----------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel A
Boolean
'If there is nothing in Sheet1 cell E59..
If Len(ThisWorkbook.Sheets("Sheet1").Range("E59").Val ue) = 0 The
'Notify the user and don't save the workbook
MsgBox "You must enter your name in cell E59 on Sheet1", , "ERROR
Cancel = Tru
End I
End Su
Paste this code into the ThisWorkbook module of the workbook where thi
validation should occur. From the Tools menu, select Macro >> Visua
Basi
Editor. This will open the Visual Basic Editor (VBE) for Excel. From th
Vie
menu in the VBE, select Project Explorer. It usually opens along th
lef
side of the screen. You should see some bold text like "VBAProjec
(Book1)"
where Book1 is the name of your workbook. Expand the indented list unde
i
and one of the items listed should say ThisWorkbook. Double-click o
ThisWorkbook and a new blank window should open to the right of th
Projec
Explorer window. Paste the code above in that window. Close the VBE an
save the file (you won't be able to unless there is something in E59).
------------------------------------------------------------------
This last sentence is "my loop"! I cannot save the code because ther
is nothing in the field but if I put something in the field to save th
code it defeats the requirement (as the user could just leave what wa
in the field vs making a valid selection)............
------------------------------------------------------------------
this is the actual code I used
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel A
Boolean
'If there is nothing in Sheet1 cell F18..
If IsEmpty(Sheets("Sheet1").Range("F18")) Or
Len(Sheets("Sheet1").Range("F18").Value) = 0 The
'Notify the user and don't save the workbook
MsgBox "You must select a boot stripe color", , "ERROR
Cancel = Tru
Exit Su
End I
End Su
---------------------------------------------------------------
2) I need to also require users to select from a drop down list or typ
in a text box if a certain check box has been ticked (i.e. If you chec
"change hull color" then you must select which color from a list or typ
in a custom color in a text bo
3) I need to require users to select from a drop down list if a chec
box is ticked (i.e. select seat X instead of seat Y - seat X require
the user to select a canvas color)
much appreciate any help anyone can give me
need to be able to do the following
1) one field is a drop down value list - I need the user to select on
of the values - I found this thread and tried it - and it almost worke
but I'm caught in a loop! I found this very helpful response to
similar question and tried it
Default Require specific cell entry before saving fil
Try this VBA event code (change the sheet name as appropriate)
-----------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel A
Boolean
'If there is nothing in Sheet1 cell E59..
If Len(ThisWorkbook.Sheets("Sheet1").Range("E59").Val ue) = 0 The
'Notify the user and don't save the workbook
MsgBox "You must enter your name in cell E59 on Sheet1", , "ERROR
Cancel = Tru
End I
End Su
Paste this code into the ThisWorkbook module of the workbook where thi
validation should occur. From the Tools menu, select Macro >> Visua
Basi
Editor. This will open the Visual Basic Editor (VBE) for Excel. From th
Vie
menu in the VBE, select Project Explorer. It usually opens along th
lef
side of the screen. You should see some bold text like "VBAProjec
(Book1)"
where Book1 is the name of your workbook. Expand the indented list unde
i
and one of the items listed should say ThisWorkbook. Double-click o
ThisWorkbook and a new blank window should open to the right of th
Projec
Explorer window. Paste the code above in that window. Close the VBE an
save the file (you won't be able to unless there is something in E59).
------------------------------------------------------------------
This last sentence is "my loop"! I cannot save the code because ther
is nothing in the field but if I put something in the field to save th
code it defeats the requirement (as the user could just leave what wa
in the field vs making a valid selection)............
------------------------------------------------------------------
this is the actual code I used
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel A
Boolean
'If there is nothing in Sheet1 cell F18..
If IsEmpty(Sheets("Sheet1").Range("F18")) Or
Len(Sheets("Sheet1").Range("F18").Value) = 0 The
'Notify the user and don't save the workbook
MsgBox "You must select a boot stripe color", , "ERROR
Cancel = Tru
Exit Su
End I
End Su
---------------------------------------------------------------
2) I need to also require users to select from a drop down list or typ
in a text box if a certain check box has been ticked (i.e. If you chec
"change hull color" then you must select which color from a list or typ
in a custom color in a text bo
3) I need to require users to select from a drop down list if a chec
box is ticked (i.e. select seat X instead of seat Y - seat X require
the user to select a canvas color)
much appreciate any help anyone can give me