Posts by jsmith2043

    Re: Unhiding Sheet based on the result of cell IF formula


    Just tried this approach, and it works when F3 goes from Yes to No, until I uncheck one of the checkboxes, sheet 2 remains visible, any ideas??


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If [F3] = "Yes" Then
    Sheets("Sheet2").Visible = True
    Else
    Sheets("Sheet2").Visible = False
    End If
    End Sub

    Re: Unhiding Sheet based on the result of cell IF formula


    Thank you sir, for your time and assistance, however, the order of events are as follows, the CheckBoxes are checked first, for instance CB3 would be "Does NOT effect others" CB4 would be "Is NOT Recordable" then those CBs would require signature sign off (via userform and password) and the date that the evaluation was made.


    In that order, the checkbox would have to be "rechecked" in the code you gave above, after all conditions were met, my apologies for the lack of communication.

    Hello gurus, I am trying to unhide a sheet when 6 conditions have been met, I'm using an IF formula to return "Yes" or "No" when those conditions are met. Explanation of conditions that must be met in the attached workbook...Cell D2 is the sum of CheckBox 3 & 4, there must be a name in A7 & A9, there must be a date in B7 & B9. F3 is the result of the Formula. I cannot seem to get the code working, if you have a better approach at what I'm trying to accomplish, by all means post it up.


    I have tried the following in the worksheet...


    Code
    Private Sub Worksheet_Calculate()
    If Me.Range(F3) = "Yes" Then
    Sheets("Sheet2").Visible = True
    Else
    Sheets("Sheet2").Visible = False
    End If
    End Sub


    I have tried the following in the workbook



    Any and all suggestions greatly appreciated.


    Joe


    Admins, my apologies, I should have created this in the Excel VBA/Macros section.

    I have the following code that "beforesave" searches the document and locks the cells that has data, and leaves empty cells unlocked. When save is clicked the userform shows. The useform is triggered by "worksheet selection change" cells B21 and B23 show userform2 and cell B37 userform1. How do I stop the userform from showing when the save button is clicked. I greatly appreciate the help and lesson. I can post the code for the userform if needed as well.


    Re: Locking Merged cells before save


    For those looking for the working code, here it is...Thank you ASHU1990, couldn't have done it without you.



    Re: Locking Merged cells before save


    A little info on this form, this form goes around the office to several different users, we do not want users editing someone elses remarks. The first user will initiate the form, that data needs to be uneditable by the next user and so on.
    The VP of Quality will hold the password for changes deemed necesssary.

    Re: Locking Merged cells before save


    Thanks for the quick reply, the code works for the initial save, subsequent saves results in...
    Runtime error "1004"
    Unable to set the locked property of the range class
    Debug line

    Code
    Activesheet.Usedrange.Locked = False

    I have a QC form that contains merged cells, I am not allowed to change the form. I need to lock the cells after data entry when saving, I have the following...



    In the attached file I have highlighted the cells that would require input, the highlights will return to "no fill" once the form is working.
    Password is blank for now.


    Thanks in advance for your help,


    Joe

    [INDENT]I have the following working code, I only need one E-mail if any or all of those cells are >0, instead of the 7 if all the cells are >0...I have tried to compile this code




    [/INDENT]

    Code
    If Worksheets("Tool Room List").Range("G546") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False   
       If Worksheets("Tool Room List").Range("G591") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False
       If Worksheets("Tool Room List").Range("G593") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False
       If Worksheets("Tool Room List").Range("G594") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False
       If Worksheets("Tool Room List").Range("G595") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False
       If Worksheets("Tool Room List").Range("G630") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False
       If Worksheets("Tool Room List").Range("G631") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False


    [INDENT]
    into this..


    Code
    If Worksheets("Tool Room List").Range("G546,G591:G595,G630:G631") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False



    But as you experts know, it doesn't work,


    Thanks in advance for your assistance,


    Joe[/INDENT]

    Re: Send Email based on a single cell value using Sub button click()


    Quote from S O;738811

    Hi jsmith2043, can you post this in a new thread please? You're nearly there with the fix but it's technically a different topic from the original query and so we would use a different thread for the sake of keeping the search facility clean within the forum :)



    Absolutely...Thanks

    Re: Send Email based on a single cell value using Sub button click()


    My list has grown over the last few months, and I've had to add more cells to act upon, what I've tried to do is compile this...


    Code
    If Worksheets("Tool Room List").Range("G527") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False
       If Worksheets("Tool Room List").Range("G591") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False   If Worksheets("Tool Room List").Range("G593") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False
       If Worksheets("Tool Room List").Range("G594") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False
       If Worksheets("Tool Room List").Range("G595") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False
       If Worksheets("Tool Room List").Range("G630") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False
       If Worksheets("Tool Room List").Range("G631") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False


    The above works, however if all of those cells are greater than 0, I get 7 Emails...I want to get only one if any or all of those cells are >0


    I tried the following...


    Code
    If Worksheets("Tool Room List").Range("G546,G591:G595,G630:G631") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False



    But as you experts probably already know...it doesn't work.


    I realize this is not the original thread topic ,and I will create a new one if required...


    Thank you in advance for the assistance!


    Joe

    Re: Send Email based on a single cell value using Sub button click()


    Thank you for the quick reply, getting Subscript out of range, I have attached the copy of the file through dropbox (its a large file). the code is in module 1http://"https://www.dropbox.com/s/99tm12uumwjhnfw/Master%20Tool%20List%20%20Latest%20Revision%20%284%29.xlsm?dl=0"







    http://"https://www.dropbox.com/s/99tm12uumwjhnfw/Master%20Tool%20List%20%20Latest%20Revision%20%284%29.xlsm?dl=0"

    The following code works flawlessly, I need to add a condition to sending the Email. If cell G527 in worksheet (Tool Room List) contains a number greater than Zero, then send the Email, if not or isblank do nothing. They way the code is below, it will send an email regardless.
    The email code is near the bottom of the code below
    Thank you in advance for your help.