+ Reply to Thread
Results 1 to 19 of 19

Making a Cell Mandatory in a workbook with multiple sheets

  1. #1
    Registered User
    Join Date
    06-22-2022
    Location
    NSW, Australia
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    Posts
    10

    Making a Cell Mandatory in a workbook with multiple sheets

    Hello,

    I am currently trying to make one cell mandatory in a specific sheet. The cell does have a Vlookup formula that is based on another cell with a dropdown. These choices from the dropdown cause a name to automatically pop up on the cell im trying to make mandatory. However this cell allows that name to be erased and another name to be put in. This functionality needs to stay as the automatic name is just the most common name for this purpose but others may be used. This means that people are able to delete the name or not even pick a choice from the dropdown and save it which causes hassle. Am I able to make this cell mandatory at all? Thanks.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,626

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    It's hard to say without seeing things in context in a sample workbook, but maybe you can set up conditional formatting on other cells which turns the font white if the drop-down cell is not equal to your chosen name, so that the sheet appears blank.

    Hope this helps.

    Pete

  3. #3
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    732

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    I think this requires VBA. If you put up your worksheet (or a representative worksheet) so I know where everything is, I'll have a look at it for you (at the very least).
    You'll probably get a few responses before I even get to see it.

  4. #4
    Registered User
    Join Date
    06-22-2022
    Location
    NSW, Australia
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    Posts
    10

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    I've attached pictures of the sheet name the cell is in,

    the lookup formula on the cell and a picture showing the location dropdown next to endorsed by.

    Endorsed by is the cell that I don't want to be left blank. As I said previously it changes based on location (left blank for confidentiality in picture) automatically but that text can just be deleted and then the worksheet can be saved.

    The endorsed by cell is L8

    Thanks
    Attached Images Attached Images

  5. #5
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    732

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    Yeah I know...'I should've gone to Specsavers' but I can't see that.

    Like I said, if you put up a worksheet, I'll have a look at it.

  6. #6
    Registered User
    Join Date
    06-22-2022
    Location
    NSW, Australia
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    Posts
    10

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    Hello,

    I have now attached the worksheet after taking off sensitive data. So as you can see the cell under "location" is a drop down which affects what pops up in the cell under "endorsed by".

    And if you just delete the text in the cell under endorsed by you can just save it and this creates hassle and follow ups

    Hopefully you can see the attached worksheet.

    If you (or anyone else) could help that would be amazing! thanks for guiding me through this so far.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    732

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    If you use this code, does it do what you want?
    Press Alt,F11 while in the 'STANDARD REQUEST' tab to open the VBA editor (or right click on the tab and select 'View Code') and paste this code in
    Please Login or Register  to view this content.
    You may also require this code in 'ThisWorkbook' module (after pasting in the above code double click on 'ThisWorkbook' in the panel on the left hand side and paste this in.
    Please Login or Register  to view this content.
    Last edited by Croweater; 06-23-2022 at 09:44 AM.

  8. #8
    Registered User
    Join Date
    06-22-2022
    Location
    NSW, Australia
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    Posts
    10

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    Hello Croweater,

    This is a great code, thank you very much! I was just wondering if instead of endorsed by not being able to be changed at all. If it can be changed but just not left blank, this is because the autopopulate on that is the most likely name that should be there however there are times where a different name should be put there.

    For example say if john smith was the most common endorser of house 1 his name would show up if house 1 was to be selected in located. However they may be away or there could be a change of responsibilities so automatically john smith would pop up but if that could also be allowed to change that would be great. Obviously this keeps with the theme that while it can be changed it cant be left blank.

    I'm thinking that maybe the auto populate for endorsed by might need to be taken away to make this possible? I don't believe that would be a problem if I must do that, just as long as you need to put something in the endorsed by.

    Thanks for your (and others) help so far.

  9. #9
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    732

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    Try this in you Standard Request Sheet;
    Please Login or Register  to view this content.
    And this in ThisWorkbook module;

    Please Login or Register  to view this content.
    PS: you don't need to take away the formula for that cell in the first instance, but as soon as they type in a name of course, it won't work for subsequent Location selections. (Although I did put back the formula if they enter it as blank).
    PPS: Not sure how your dropdown list in cell H8 is setup as it wasn't defined in your attachment, but you might want to uncheck 'ignore blank' as a blank in that cell will give you an error in your formula in cell L8
    Last edited by Croweater; 06-23-2022 at 07:41 PM.

  10. #10
    Registered User
    Join Date
    06-22-2022
    Location
    NSW, Australia
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    Posts
    10

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    Hey Croweater,

    Thanks for all your help so far this is great. I decided to get rid of the autopopulate for endorsed by as it makes it easier anyway.

    I have found that the code works and i save it as an xlsm file. I quit out of it through task manager as I want it to be blank to start with when distributed.

    When opening it again it has the code still in the visual basic but I am able to quit out of it while endorsed by is blank.

    Do you know if I am saving it wrong or something like that?

    Thanks.

  11. #11
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    732

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    OK. I did exactly the same thing as you did, now I have an empty worksheet and it will NOT let me quit while endorsed by is blank.

    Did you put the code in the correct class modules (One in Standard Request and one in ThisWorkbook)?
    As you have got rid of the autopopulate, did you take this line out?
    Please Login or Register  to view this content.
    Otherwise, post up the worksheet that is not working and I'll see what you have done.

  12. #12
    Registered User
    Join Date
    06-22-2022
    Location
    NSW, Australia
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    Posts
    10

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    Hey Croweater,

    Here is the worksheet, I have taken that line out now as well but the problem hasn't changed for me.

    Appreciate your continued help!
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    732

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    Are you opening it in 'protected view'? If you enable editing on that file, it should work. It does for me.

  14. #14
    Registered User
    Join Date
    06-22-2022
    Location
    NSW, Australia
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    Posts
    10

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    Oh yes it seems to be working now thanks very much!

    So they won't be able to save this at all before entering something in the endorsed by right?

    Seems great!

    Thanks very much!

  15. #15
    Registered User
    Join Date
    06-22-2022
    Location
    NSW, Australia
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    Posts
    10

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    Yep I was able to make it stop saving as well with a tiny change.

    Learned something myself

    Thanks again!!!

  16. #16
    Registered User
    Join Date
    06-22-2022
    Location
    NSW, Australia
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    Posts
    10

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    Actually might need one more thing if possible aha.

    So i just copied the previous code for not allowing closing before endorsed by is filled and replaced first bit to before save.

    I have now realised it doesn't let me save the code unless endorsed by is filled however I want that to be blank when it is opened.

    Is it possible at all to save this code while it is contradicting its own rule? sorry aha

    Thanks

  17. #17
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    732

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    Well, I'm not sure how you have changed what I gave you, but (with the original code I supplied) you could save it when it is blank (without exiting), then put a name in endorsed by and save as something else. Then exit and delete the one that has the name in.

    Then you will have a worksheet with a blank name in the 'endorsed by' cell which will not let you exit without filing that in. Is that what you want?
    Last edited by Croweater; 06-24-2022 at 01:05 AM.

  18. #18
    Registered User
    Join Date
    06-22-2022
    Location
    NSW, Australia
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    Posts
    10

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    It seems as soon as i try to put in the code for before saving, it will not let me save so I dont know if im able to do that.

    I've attached the worksheet with the before close codes in and this is the code i made for before save:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Sheets("STANDARD REQUEST").Range("L8").Value = "" Then
    Cancel = True
    MsgBox "Endorsed By name cannot be blank"
    Else
    ActiveWorkbook.Close SaveChanges:=True
    End If
    End Sub

    So when i put that in below the before close code it wont let me save the code or sheet at all.

    Thanks,
    Attached Files Attached Files

  19. #19
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    732

    Re: Making a Cell Mandatory in a workbook with multiple sheets

    Use BeforeClose...NOT BeforeSave

    1 Make endorsed By Blank.
    2. Save File using your desired name (The one you are going to send to your users)
    3. Save As a different name (eg. Rubbish.xlsm)
    4. put a name in endorsed by
    5. Exit.

    You will now have 2 files. One with a 'proper' name with the 'endorsed by' cell blank. This is what you will give to your users.
    Another file with a name in it called 'Rubbish.xlsm', which you can delete

    When the users open the file you have given them they won't be able to exit, without having first entered a name in the 'endorsed by' cell.
    When they exit, the file will be saved automatically.

    I just tried all of those steps with the file you attached, and it worked.

    Just as an after thought, you may want to change
    Please Login or Register  to view this content.
    To
    Please Login or Register  to view this content.
    Last edited by Croweater; 06-24-2022 at 01:56 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Help w/ Making Cell Mandatory - How to Save Blank Template
    By GregStewartPTC in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-04-2017, 03:06 PM
  2. VBA - Making column's mandatory
    By snoopy1461 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-28-2011, 04:06 AM
  3. VBA - Making column's mandatory
    By snoopy1461 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2011, 01:36 PM
  4. Making a cell mandatory
    By Abgirl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2010, 04:37 PM
  5. [SOLVED] Making cells mandatory to fill in if a previous cell contains info
    By leonardo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-24-2006, 02:50 PM
  6. Making Cell Entry Mandatory
    By Sue T in forum Excel General
    Replies: 4
    Last Post: 08-17-2005, 11:06 AM
  7. [SOLVED] Is there a way of making data imput in to a cell mandatory before.
    By TerryM in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-15-2005, 09:35 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1