Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 6th 12, 05:20 PM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 3
Default I'm caught in a loop - Require specific cell entry before saving file

I have an excel template that I have set up to use as an order form. I need to be able to do the following:

1) one field is a drop down value list - I need the user to select one of the values - I found this thread and tried it - and it almost worked but I'm caught in a loop! I found this very helpful response to a similar question and tried it:

Default Require specific cell entry before saving file
Try this VBA event code (change the sheet name as appropriate):
------------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'If there is nothing in Sheet1 cell E59...
If Len(ThisWorkbook.Sheets("Sheet1").Range("E59").Val ue) = 0 Then
'Notify the user and don't save the workbook.
MsgBox "You must enter your name in cell E59 on Sheet1", , "ERROR"
Cancel = True
End If
End Sub

Paste this code into the ThisWorkbook module of the workbook where this
validation should occur. From the Tools menu, select Macro Visual Basic
Editor. This will open the Visual Basic Editor (VBE) for Excel. From the View
menu in the VBE, select Project Explorer. It usually opens along the left
side of the screen. You should see some bold text like "VBAProject (Book1)",
where Book1 is the name of your workbook. Expand the indented list under it
and one of the items listed should say ThisWorkbook. Double-click on
ThisWorkbook and a new blank window should open to the right of the Project
Explorer window. Paste the code above in that window. Close the VBE and 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 there is nothing in the field but if I put something in the field to save the code it defeats the requirement (as the user could just leave what was in the field vs making a valid selection).............
-------------------------------------------------------------------
this is the actual code I used:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'If there is nothing in Sheet1 cell F18...
If IsEmpty(Sheets("Sheet1").Range("F18")) Or _
Len(Sheets("Sheet1").Range("F18").Value) = 0 Then
'Notify the user and don't save the workbook.
MsgBox "You must select a boot stripe color", , "ERROR"
Cancel = True
Exit Sub
End If
End Sub
----------------------------------------------------------------
2) I need to also require users to select from a drop down list or type in a text box if a certain check box has been ticked (i.e. If you check "change hull color" then you must select which color from a list or type in a custom color in a text box

3) I need to require users to select from a drop down list if a check box is ticked (i.e. select seat X instead of seat Y - seat X requires the user to select a canvas color).

much appreciate any help anyone can give me!

Last edited by edentonmomster : September 6th 12 at 05:31 PM

  #2   Report Post  
Old September 6th 12, 08:29 PM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 3
Smile

problem solved - thanks everyone!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Require input in a cell before saving file Patrick Riley Excel Discussion (Misc queries) 21 March 26th 08 02:47 PM
Require specific cell entry before saving file Patrick Riley Excel Discussion (Misc queries) 1 March 21st 08 04:46 PM
Require specific cell entry before saving file Patrick Riley Excel Programming 0 March 21st 08 04:03 PM
Require specific cell entry before saving file Patrick Riley Excel Programming 3 March 21st 08 08:13 AM
Require specific cell entry before saving file Patrick Riley Excel Discussion (Misc queries) 2 March 20th 08 03:50 PM


All times are GMT +1. The time now is 12:24 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017