Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Message Box (Office 2003 Service Pack 2)

    Hello

    I would like a message box to open automatically when a workbook is opened, I have this code

    Dim rc As Variant
    rc = ktMsgBox(" CHANGES TO LEAVE PLANNER" & vbCrLf & "" & vbCrLf & "1 - Search Facility | Use the search facility to check what holiday dates a particular employee has " & vbCrLf & " already taken" & vbCrLf & "" & vbCrLf & "2 - Authorised By | When completing the form it is now a requirement to add the name of the person " & vbCrLf & " that has authorised the leave." & vbCrLf & "" & vbCrLf & "3 - Changes | The leave planner is now completely password protected and can only be changed by " & vbCrLf & " Paul or Roger on receipt of an email." & vbCrLf & "" & vbCrLf & "Any problems, questions, suggestions please call me on xt. 1027" _
    , vbOKOnly + kt_MsgBox_PinIcon, "Fish Food Emporium" _
    , BackColor:=&HCCFFFF& _
    , BackImage:="Cocuments and SettingsRogerMy DocumentsMy PicturesNew Logo.bmP" _
    , ImageWidth:=10 _
    , ImageHeight:=15)

    But what do I have to do to make it work and where do I put it?

    Thank you

    Roger

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message Box (Office 2003 Service Pack 2)

    Activate the Visual Basic Editor.
    Locate your workbook in the Project Explorer on the left hand side.
    If necessary expand Microsoft Excel Objects.
    Double click ThisWorkbook.
    The code module for workbook-level event procedures will open.
    Select Workbook from the dropdown list in the upper left corner of the module window.
    This will create the "skeleton" for the Workbook_Open event procedure; this will be executed automatically each time the workbook is opened.
    Insert the code you want:

    Private Sub Workbook_Open()
    Dim rc As Variant
    rc = ktMsgBox(" CHANGES TO ...
    ...
    , ImageHeight:=15)
    End Sub

    You can add more code to the procedure if you wish.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Message Box (Office 2003 Service Pack 2)

    Just for info, the shortcut for the first 4 steps of Hans' solution (to get to the ThisWorkbook module) is to right-click the Excel icon to the left of 'File' on the main Excel menu (or on the left of the workbook window if it's not maximised), and choose 'View Code'
    FWIW
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Message Box (Office 2003 Service Pack 2)

    Thanks for that Rory. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards
    Don

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Message Box (Office 2003 Service Pack 2)

    No problem - saves me a good few seconds every day... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message Box (Office 2003 Service Pack 2)

    Hello

    Thank you for that, I can now get the message box to appear but i wanted to Customise it a little, this is the code I have used

    Private Sub Workbook_Open()
    Dim rc As Variant
    rc = MsgBox("Service Updates" & vbCrLf & "" & vbCrLf & "1. Special offers this week - Carp bait - Push for +5 commission" & vbCrLf & "2. Sale Items (discontinued line) - Radar Nav 2010 - 240" & vbCrLf & "3. Top sales so far this month - Ivan 3200, Mark 3100 (Tracy 443??) - 50 top prize this month" & vbCrLf & " " & vbCrLf & "Any Problems call Roger / HQ / 1011" _
    , vbOKOnly, "FISH FOOD EMPORIUM" _
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">, PromptColor:=&H800000 _
    , BackColor:=&HCCFFFF _
    , FontSize:=9)</span hi>
    End Sub

    But unless I delete the highlighted lines it will not work, my intention was to have the font dark blue (,PromptColor:=&H800000 ), the background colour sand ( , BackColor:=&HCCFFFF) and the font sized as 9 but I cannot see why it does not like these 3 lines

    Any suggestions please

    Thank you

    Roger

  7. #7
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message Box (Office 2003 Service Pack 2)

    Hello

    Thanks for the tip Rory

    Roger

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message Box (Office 2003 Service Pack 2)

    The built-in MsgBox function in VBA doesn't support customizing fonts and colors, its syntax is

    MsgBox(Prompt, Buttons, Title, HelpFile, Context)

    Your original question in this thread had ktMsgBox, which is a custom MsgBox on steroids from AddinBox( ktMsgBox function/EXCEL )

    Does your code work with ktMsgBox instead of MsgBox?

  9. #9
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message Box (Office 2003 Service Pack 2)

    Hello

    Yes, that is where I started but on all my machines - office PC, Office laptop and home laptop - I load the Add in, I can see the Add In in the Add In Manager but it does not add anything to the toolbar or at the bottom of any of the menu's so I thought that using the idea of the code I could customise it, but never mind, we will have to suffer the MS offering <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Thanks for your other help

    Roger

  10. #10
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message Box (Office 2003 Service Pack 2)

    For anybody that is interested in having a bespoke message form:

    Playing about I discovered that I could make a normal UserForm that I could then colour how I wanted and to have the text how I wanted, I added a button with 'OK' on it and then coded it as me.hide, in the workbook_open() event I put UserForm1.show with the result that the form appears on opening and the OK button closes it.

    Most of you probably knew that already but I thought it worth noting for casual users like me.

Posting Permissions

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