Posts by BenC1985

  • Check .SentOnBehalfOf before sending mail

    Hey guys, wondering if anybody can provide any help with an Outlook VBA task that I have.



    I'm using Outlook 2007 but need the code to work on 2010 also. What I want to acheive is code that runs when an email is sent. If it is sent from a specific shared mailbox I want to check the body of the email for a particular sentence. If it is true, send the item, if it is not true, I want to prompt the user with a msgbox saying "Are you sure you want to send it from this email address?"



    To give you an example of the code I have, I'll post it below. The issue with this code is that it displays the prompt no matter what the .sentOnBehalfOf Email Address is. I have this code pasted in 'ThisOutlookSession'


  • Outlook macro to open Excel and check email addresses

    Re: Outlook macro to open Excel and check email addresses


    Where are you copying the email addresses from? You may be easier to copy them into an excel worksheet to start with where all of the know bad email addresses are, have a macro find and delete duplicates, then paste the cleaned list into a new email in outlook.

  • Highlight matching cells based on manual text

    Re:

    Code
    Highlight matching cells based on manual text


    Would you be better to have a list of names, like you have in cell 'B5' and put a drop-down list into the cells you want them to type their name. That way there will be no mistake.


    In your conditional format you would select "Format Cells that only Contain" Then select 'Cell Value', 'Equal to' 'B1'


    ???

  • Date Format in userform textbox where range is offset from lastrow

    Re: Date Format in userform textbox where range is offset from lastrow


    Nearly there Roy!


    Can't get my head around searching for the date in UK Format.


    Using the methods you mentioned above, this just brings back a 'True' or 'False' result.


    Any Ideas?


    Code
    With Me    'load entry to form                .ComboBox1.Value = c.Offset(0, 1).Value
                .ComboBox2.Value = c.Offset(0, 2).Value
                .ComboBox3.Value = c.Offset(0, 3).Value
                .ComboBox4.Value = c.Offset(0, 4).Value
                .TextBox2.Value = c.Offset(0, 5).Value
                .TextBox3.Value = c.Offset(0, 6).Value
                .TextBox4.Value = c.Offset(0, 7).Value

  • Counting leasing dates that will expire in 30/60/90 days

    Re: Counting leasing dates that will expire in 30/60/90 days


    I would have the =TODAY() somewhere in your worksheet lets say Cell A1
    Lets also say your lease expiration date is in cell B1
    Then add another column to what you already have lets say in C1. Put the formula =SUM(A1-B1) into cell C1 and change the cell format to number. use a countif formula to find where cell c1 hold 30, 60 or 90.

  • Date Format in userform textbox where range is offset from lastrow

    Hi everybody!


    finally completed my userform! I have got it to create a record on the next available row, search for a record and update it...


    it has severely affected my brain cells and I have just stumbled upon my (hopefully) final hurdle. I hope somebody can help with.


    The date format is MM/DD/YYYY and I need it to be DD/MM/YYYY. I have seen many posts saying to use an addin or something else that needs an activeX control but I can't use this. I have also seen bits of code to solve this but they all set a range. My range is an offset though from the last empty row. please see my code.


    This is what I have to Create a new record



    Update record


    sorry if this looks messy im writing this on my phone

  • Search and Edit excel data in a userform

    Hi everyone!


    I am really hoping someone can help me with this as I have sifted through a lot of unanswered or unclear threads regarding this.


    Basically, I work in a small team of 5 people, but each of us is very busy and get emails from our manager asking us to complete different tasks. I have made a userform in excel for our manager to create the tasks in and put all the details in sheet1. This creates each separate task on a separate row. Now I want each of my colleagues to log into the sheet and be able to retrieve a task with the same userform. I am giving each task it's own ID, so I want to be able to search by this and the userform will be populated with the task details. I also have combobox's with the status of each task (Completed, In progress etc.) I want to be able to change these in the userform too.


    I would do this in access, but we don't have this on our work laptops.


    My code for the userform up to now is:



    Any help at all would be appreciated.

  • select worksheet based on month

    Re: select worksheet based on month


    Hi Roland,


    I'm new to this also, but I think the answer to your question is on a previous thread:


    HTML
    http://www.ozgrid.com/forum/showthread.php?t=59691


    Please note, this code does not go by Sheet name, it goes by the sheet reference so if you change the name of the sheet, the code will not be affected.


    Right Click on the tab you have names Z1, select 'View Code' and this should say something like "Sheet1 (Z1)", change the sheet number in the code to reflect your worksheet.


    Code
    Sub SelectWorkheet()
    Dim strWsName As String
     
    strWsName = Sheet1.Range("B17")
    Sheets(strWsName).Select
End Sub


    I've not tested it, as I found it on a previous thread.


    Hope this helps!

  • Unhide Sheet2 and its rows from a button on Sheet1

    I have a spreadsheet where I want to hide empty rows and sheets if they are not going to be used. Because I have a few sheets that I want to have these macros on, I have put all the buttons on the first sheet.


    I want a macro that will unhide all of the rows, but on the same button I also want it to unhide the sheet if it is hidden. What I have at the minute is:


    Code
    Sub Unhide()
' Macro4 Macro
    Sheets("Sheet1").Select
    Sheets("Sheet2").Visible = True
    Rows("15:316").Select
    Selection.EntireRow.Hidden = False
    Range("B1").Select
    Sheets("Sheet1").Select
    Range("A1").Select
End Sub


    Sheet1 is where I want all the buttons
    Sheet2 is the sheet that may be hidden and may have hidden rows


    This macro unhides Sheet2 but selects and unhides rows 15:316 on Sheet1, which were never hidden.


    Any help would be great!

  • Looping Macro's with merged cells

    Re: Looping Macro's with merged cells


    Changes made to cell G16:Z16 I would like the username and date putting in cell G18:Z18 (2 cells down from the changed cell)


    Repeated to Rows G313:Z313 - username and date in G315:Z315