Posts by corcelle

  • Multiple "Ifs" to Hide Rows in a Macro

    Hi Mumps - Thanks for responding.


    No, the values are not "A, B, C" - they are a list of marketing programs for my company. There are 15 of them, of varying lengths.
    Actually, upon further review, the pattern should be to ALWAYS unhide row 7, then, in order, unhide 8 - 22 with their corresponding selection.


    KjBox -- also thank you!
    The dropdown is currently a list validation.

  • Multiple "Ifs" to Hide Rows in a Macro

    Hello,


    I'm attempting to write a macro that will display results based on the entry in a few fields. I've programmed the workbook to hide pertinent rows upon opening the file. I want it to then unhide rows based on the value of cell B2, which is a drop-down menu with 15 options.


    If option "A" is selected, I want it to unhide rows 7 & 8
    If option "B" is selected, I want it to unhide rows 9 & 10


    . . . and so forth.


    I also want to make sure that as the value of cell B2 is changed, only the corresponding rows are displayed (so previously unhidden rows need to be rebidden).


    I can't seem to figure out how to put more than one if/else statement in succession to make this happen!


    All help is much appreciated.


    Thank you!

  • Format a cell based on multiple checkbox combinations

    Hi there,


    Is it possible to have a macro that allows for a combination of IF and OR statements? I want to have a cell change format (color, bold, doesn't matter) based on whether certain factors are true. The trouble is, there may be more than one combination of factors that could lead to that cell being formatted. In laymen's terms:


    If A1, A2, & A3 are TRUE, OR IF A1, A2, & A4 are TRUE, OR IF A2, A4, A5 are TRUE . . . .then format B1.


    Help is much appreciated!!

  • Send Hyperlink Through Lotus Email in VBA

    Hello,


    I have the macro written below within a workbook. It's purpose is to send an email notification (in Lotus) when the button is hit, and it works great except the recipient of the email wants the location to be a hyperlink, and I can't figure out how to do that. Can anyone help? The part that requires the hyperlink is in the message body where it says "Checklist Location: " I basically want a link to where the checklist is saved, which is in a Sharepoint site.


    Thank you for your help!



    [/COLOR][/SIZE][/FONT]

  • Need to Embed Hyperlink in VBA Email (Lotus Notes)

    Re: Need to Embed Hyperlink in VBA Email (Lotus Notes)


    I have looked through a ton of answers, but none that I could just add to my existing code (all were entirely different codes that I'm not sure what they mean/how to use them). I was hoping for just a fix to my existing code.

  • Need to Embed Hyperlink in VBA Email (Lotus Notes)

    Hello,


    I have the following code sending an email. I want the link following "Checklist location" in the body of the email to be a hyperlink, but right now it is pulling the link as text and cannot be clicked on. Can anyone suggest a way to change this code to create the hyperlink when the email is sent?


    Thank you in advance for all help.
    Laura



    Code
    Sub SendInitiationEmail()[/SIZE][/FONT] [FONT=sans-serif][size=10]    Dim noSession As Object, noDatabase As Object, noDocument As Object[/SIZE][/FONT] [FONT=sans-serif][size=10]    Dim obAttachment As Object, EmbedObject As Object[/SIZE][/FONT] [FONT=sans-serif][size=10]    Dim stSubject As Variant, stAttachment As String[/SIZE][/FONT] [FONT=sans-serif][size=10]    Dim vaRecipient As Variant[/SIZE][/FONT] [FONT=sans-serif][size=10]    Dim vaMsg As Variant[/SIZE][/FONT] [FONT=sans-serif][size=10]    Dim valink As Variant[/SIZE][/FONT] [FONT=sans-serif][size=10]    Const EMBED_ATTACHMENT As Long = 1454[/SIZE][/FONT] [FONT=sans-serif][size=10]    Const stTitle As String = "Status Active workbook"[/SIZE][/FONT] [FONT=sans-serif][size=10]    Const stMsg As String = "The active workbook must first be saved " & vbCrLf _[/SIZE][/FONT] [FONT=sans-serif][size=10]     & "before it can be sent as an attachment."[/SIZE][/FONT] [FONT=sans-serif][size=10]   'If the active workbook has not been saved at all.[/SIZE][/FONT] [FONT=sans-serif][size=10]    If Len(ActiveWorkbook.Path) = 0 Then[/SIZE][/FONT] [FONT=sans-serif][size=10]        MsgBox stMsg, vbInformation, stTitle[/SIZE][/FONT] [FONT=sans-serif][size=10]        Exit Sub[/SIZE][/FONT] [FONT=sans-serif][size=10]    End If[/SIZE][/FONT] [FONT=sans-serif][size=10]    With Worksheets(1)[/SIZE][/FONT] [FONT=sans-serif][size=10]        .Hyperlinks.Add .Range("CC1"), ActiveWorkbook.FullName[/SIZE][/FONT] [FONT=sans-serif][size=10]    End With[/SIZE][/FONT] [FONT=sans-serif][size=10]    Dim x As Integer[/SIZE][/FONT] [FONT=sans-serif][size=10]    For x = 1 To 1[/SIZE][/FONT] [FONT=sans-serif][size=10]        'Get the name of the recipient from the user.[/SIZE][/FONT] [FONT=sans-serif][size=10]        vaRecipient = "[EMAIL="[email protected]"][email protected][/EMAIL]bosmad.com"[/SIZE][/FONT] [FONT=sans-serif][size=10]    Do 'Get the message from the user.[/SIZE][/FONT] [FONT=sans-serif][size=10]           vaMsg = "Hello," & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10]            "Please refer to the link below for the following campaign checklist, which is now initated and stored in the repository location below:" & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10]            "Campaign:" & " " & Worksheets("Current Campaign - Final").Range("B4") & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10]            "Program/Offer:" & " " & Worksheets("Current Campaign - Final").Range("B5") & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10]            "Checklist Location:" & " " & Worksheets("Current Campaign - Final").Range("CC1") & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10]            "Please make sure that all tasks are signed off by placing the date and your initials on each line once completed." & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10]           "Thank you!" & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10]           "PMCM Quality Manager"[/SIZE][/FONT] [FONT=sans-serif][size=10]           [/SIZE][/FONT] [FONT=sans-serif][size=10]           [/SIZE][/FONT] [FONT=sans-serif][size=10]           [/SIZE][/FONT] [FONT=sans-serif][size=10]    Loop While vaMsg = ""[/SIZE][/FONT] [FONT=sans-serif][size=10]    If vaMsg = False Then Exit Sub   'If the user has canceled the operation.[/SIZE][/FONT] [FONT=sans-serif][size=10]    'Add the subject to the outgoing e-mail which also can be retrieved from the users[/SIZE][/FONT] [FONT=sans-serif][size=10]    'in a similar way as above.[/SIZE][/FONT] [FONT=sans-serif][size=10]     stSubject = Worksheets("Current Campaign - Final").Range("B2").Value & "- Usage Initiated Checklist"[/SIZE][/FONT] [FONT=sans-serif][size=10]    'Retrieve the path and filename of the active workbook.[/SIZE][/FONT] [FONT=sans-serif][size=10]     stAttachment = ActiveWorkbook.FullName[/SIZE][/FONT] [FONT=sans-serif][size=10]    'Instantiate the Lotus Notes COM's Objects.[/SIZE][/FONT] [FONT=sans-serif][size=10]     Set noSession = CreateObject("Notes.NotesSession")[/SIZE][/FONT] [FONT=sans-serif][size=10]     Set noDatabase = noSession.GETDATABASE("", "")[/SIZE][/FONT] [FONT=sans-serif][size=10]     'If Lotus Notes is not open then open the mail-part of it.[/SIZE][/FONT] [FONT=sans-serif][size=10]      On Error Resume Next[/SIZE][/FONT] [FONT=sans-serif][size=10]      If noDatabase.IsOpen = False Then noDatabase.OPENMAIL[/SIZE][/FONT] [FONT=sans-serif][size=10]    'Create the e-mail[/SIZE][/FONT] [FONT=sans-serif][size=10]     Set noDocument = noDatabase.CreateDocument[/SIZE][/FONT] [FONT=sans-serif][size=10]    'Add values to the created e-mail main properties.[/SIZE][/FONT] [FONT=sans-serif][size=10]     With noDocument[/SIZE][/FONT] [FONT=sans-serif][size=10]        .Form = "Memo"[/SIZE][/FONT] [FONT=sans-serif][size=10]        .sendto = vaRecipient[/SIZE][/FONT] [FONT=sans-serif][size=10]        .Subject = stSubject[/SIZE][/FONT] [FONT=sans-serif][size=10]        .Body = vaMsg[/SIZE][/FONT] [FONT=sans-serif][size=10]        .SaveMessageOnSend = True[/SIZE][/FONT] [FONT=sans-serif][size=10]     End With[/SIZE][/FONT] [FONT=sans-serif][size=10]    'Send the e-mail.[/SIZE][/FONT] [FONT=sans-serif][size=10]     Dim myMessage As String[/SIZE][/FONT] [FONT=sans-serif][size=10]     myMessage = MsgBox("Are you sure you want to send your initiated Checklist location?", vbYesNo, "Are you sure?")[/SIZE][/FONT] [FONT=sans-serif][size=10]     If myMessage = vbYes Then[/SIZE][/FONT] [FONT=sans-serif][size=10]         With noDocument[/SIZE][/FONT] [FONT=sans-serif][size=10]             .PostedDate = Now()[/SIZE][/FONT] [FONT=sans-serif][size=10]             .SEND 0, vaRecipient[/SIZE][/FONT] [FONT=sans-serif][size=10]         End With[/SIZE][/FONT] [FONT=sans-serif][size=10]    'Release objects from the memory.[/SIZE][/FONT] [FONT=sans-serif][size=10]    Set EmbedObject = Nothing[/SIZE][/FONT] [FONT=sans-serif][size=10]    Set obAttachment = Nothing[/SIZE][/FONT] [FONT=sans-serif][size=10]    Set noDocument = Nothing[/SIZE][/FONT] [FONT=sans-serif][size=10]    Set noDatabase = Nothing[/SIZE][/FONT] [FONT=sans-serif][size=10]    Set noSession = Nothing[/SIZE][/FONT] [FONT=sans-serif][size=10]       'Activate Excel for the user.[/SIZE][/FONT] [FONT=sans-serif][size=10]       AppActivate "Microsoft Excel"[/SIZE][/FONT] [FONT=sans-serif][size=10]          MsgBox "The e-mail has successfully been created and distributed.", vbInformation, "Done!"[/SIZE][/FONT] [FONT=sans-serif][size=10]      Else[/SIZE][/FONT] [FONT=sans-serif][size=10]       MsgBox "Unsent email!", vbInformation, "Unsent email"[/SIZE][/FONT] [FONT=sans-serif][size=10]      End If[/SIZE][/FONT] [FONT=sans-serif][size=10]     Next x[/SIZE][/FONT] [FONT=sans-serif][size=10]End Sub[/SIZE][/FONT][/FONT][/COLOR]

  • Macro to Hide/Unhide Rows Based on Checkbox Controls

    Hello,
    I have built checkboxes at the top of my workbook that hide or unhide certain rows based on whether they are unchecked/checked. Users can select which rows they want to see by selecting the corresponding boxes.


    I want to create a "See All" selection that will unhide EVERY row if checked, but only show the rows that are selected in the other boxes if unchecked. Is this possible?


    Thanks,
    Corcelle

  • Macro to Filter/Save As Not Working

    Re: Macro to Filter/Save As Not Working


    Sorry it took me a while to respond to this. I replaced my code with your code above, and it created a new file called MF Tasks Export and saved it to the Desktop, but the file is blank/has no content.


    Any thoughts?

  • Macro to Filter/Save As Not Working

    Re: Macro to Filter/Save As Not Working


    Thank you for your help. It is giving me an error on the following line, saying I cannot change part of a merged cell.


    Selection.SpecialCells(xlCellTypeVisible).Copy Destination:=NewWB.Sheets(1).Cells(1, 1)

  • Macro to "Save As" to User's Desktop

    Re: Macro to "Save As" to User's Desktop


    Hi!


    Huge thanks, this worked like a charm. But, now for some reason it's not hiding the rows I need before saving the new document. My ultimate goal is to hide all rows where Column C is not MF, then do the save us function. Any thoughts?

  • Macro to Filter/Save As Not Working

    Hello,


    I wrote the following macro, and it worked for a while, then I had to make a tweak and it is no longer working. The goal is to hide certain rows, open that filtered view in a new workbook, and save that workbook to the user's desktop:



    Not sure where I went wrong -- help appreciated!
    Thanks,
    Corcelle

  • Macro to "Save As" to User's Desktop

    Hello,
    I recorded the below macro to hide certain information in a workbook, then create a copy of that view and save it. When I recorded it, I saved it to my desktop; what I want it to do is save to the desktop of the active user.


    Is there a way to change to code below (Starting with the ActiveWorkbook.SaveAs) command to make this work for any computer?


    Thank you!
    Corcelle



  • Executing a formula based on an IF statement

    Hello,
    I am trying to calculate the % complete (non-blank cells) in the range I76:J106. However, I only want to count the cells in that range if A76:A106="Pre".


    I can calculate the % complete of the total range with =(COUNTIF(I76:J106,"<>"))/(ROWS(I76:I106))), field formatted as a percentage. However, I cannot figure out how to layer in the condition to only calculate rows that have "Pre" in column A.


    I tried =IF(A76:J106,"Pre",((COUNTIF(I76:J106,"<>"))/(ROWS(I76:I106)))) but I got a #VALUE error.


    Can anyone help? Thank you in advance!