+ Reply to Thread
Results 1 to 3 of 3

Excel 2003 VBA question

  1. #1
    Hogan's Goat
    Guest

    Excel 2003 VBA question

    Hi, sorry if I appear totally lame but I know just enough VBA to be
    highly dangerous. I am trying to make a small dialog that will do many
    things, including email the active workbook to user's choice of email
    addresses.

    I have checkboxes by each name which will correspond to a specific email
    address, plus one with a text box that will allow a user to supply an
    email address, like this:

    cbCustomer
    |-tbCustomerEmail
    cbSales
    cbAdmin
    cbMfg
    cbSupport
    cbMgmt

    What I want to do is first check to see if all of the checkboxes are not
    selected, and throw up a msgbox, then build an array based out of the
    selected checkboxes and add them into this line of code:

    ActiveWorkbook.SendMail Recipients:=arrayEmail, Subject:="Please
    process this order immediately"
    Application.Dialogs(xlDialogSendMail).Show

    My questions:
    1 - How can I build the arrayEmail depending on which checkboxes are
    selected?
    2 - Is that the best way to email the active workbook? It pops up
    the dialog about "Something is trying to send email from this app - OK?"
    which I would rather not have.

    Best regards,
    Me


  2. #2
    Bill Linker
    Guest

    RE: Excel 2003 VBA question

    Well, looking at the events available to checkboxes...there isn't much that
    intrinsically gives you much help. Un-needed mouse and keyboard events and
    the "default" event "Change". This event only tells you that the value
    changed...so you still have to check the value to determine the current
    value....not very useful to bother checking because you do not know if the
    user is even done making changes yet.

    I made something similar to this to allow batch processing of multiple
    reports. I just have a "Done" and "Cancel" button and do not bother checking
    the state of checkboxes until the user clicks "Ok". I just use a series of
    If statements, here are the first two from my program:

    Private Sub ButtonOK_Click()

    If (CheckBoxPSOS.Value = True) Then
    Call formatPSOS(TextBoxPSOS.Value, useDefault)
    End If
    If (CheckBoxInventory.Value = True) Then
    Call formatDailyInv(TextBoxInventory.Value, useDefault)
    End If

    ....

    In your case you have 6 possible recipients. Make a temp array of 6
    elements. Add the beginning of the Click() event initialize a counter
    (recipientCount) to zero. Then check each of your checkboxes and add email
    addresses as needed and increment the recipientCount (also your array index)
    before examining the next checkbox. When you are done, check your counter,
    if still 0, use the built-in MsgBox(strMsg as string) function to popup a
    warning message. Otherwise create a new array of the correct size (using
    Redim) and copy your email list over to the new object.

    The warning message "Something is trying to send email from this app - OK?"
    is to allow you to stop a potentially malicious macro from sending email. I
    am not sure if this can be turned off, but if so, it would probably be by
    setting Macro Security to Low (Tools->Macros->Macro Security). You may also
    be able to set yourself up as a trusted publisher (I am not familiar with
    that process).


    If the sendMail function will work with a recipient string like
    "[email protected]; [email protected]; [email protected]" (i.e. semi-colon
    or comma separated list) you could avoid arrays and just append addresses to
    your string.

    "Hogan's Goat" wrote:

    > Hi, sorry if I appear totally lame but I know just enough VBA to be
    > highly dangerous. I am trying to make a small dialog that will do many
    > things, including email the active workbook to user's choice of email
    > addresses.
    >
    > I have checkboxes by each name which will correspond to a specific email
    > address, plus one with a text box that will allow a user to supply an
    > email address, like this:
    >
    > cbCustomer
    > |-tbCustomerEmail
    > cbSales
    > cbAdmin
    > cbMfg
    > cbSupport
    > cbMgmt
    >
    > What I want to do is first check to see if all of the checkboxes are not
    > selected, and throw up a msgbox, then build an array based out of the
    > selected checkboxes and add them into this line of code:
    >
    > ActiveWorkbook.SendMail Recipients:=arrayEmail, Subject:="Please
    > process this order immediately"
    > Application.Dialogs(xlDialogSendMail).Show
    >
    > My questions:
    > 1 - How can I build the arrayEmail depending on which checkboxes are
    > selected?
    > 2 - Is that the best way to email the active workbook? It pops up
    > the dialog about "Something is trying to send email from this app - OK?"
    > which I would rather not have.
    >
    > Best regards,
    > Me
    >
    >


  3. #3
    Hogan's Goat
    Guest

    RE: Excel 2003 VBA question

    OK, let me simplify my question a little:

    I have five checkboxes within a frame on a form I am building. They are
    linked to a button. If you click the button when none of the checkboxes
    are checked, I want to throw up a MsgBox sanying, "Woopsy! Check a box
    first."

    Must I build an array of the checkboxes and, when clicking on the button,
    check for the .Enabled value of each? How would you do it?

    Thanks!

    Here's a cheerful little earful from =?Utf-8?B?QmlsbCBMaW5rZXI=?=:

    > Well, looking at the events available to checkboxes...there isn't much
    > that intrinsically gives you much help. Un-needed mouse and keyboard
    > events and the "default" event "Change". This event only tells you
    > that the value changed...so you still have to check the value to
    > determine the current value....not very useful to bother checking
    > because you do not know if the user is even done making changes yet.
    >
    > I made something similar to this to allow batch processing of multiple
    > reports. I just have a "Done" and "Cancel" button and do not bother
    > checking the state of checkboxes until the user clicks "Ok". I just
    > use a series of If statements, here are the first two from my program:
    >
    > Private Sub ButtonOK_Click()
    >
    > If (CheckBoxPSOS.Value = True) Then
    > Call formatPSOS(TextBoxPSOS.Value, useDefault)
    > End If
    > If (CheckBoxInventory.Value = True) Then
    > Call formatDailyInv(TextBoxInventory.Value, useDefault)
    > End If
    >
    > ...
    >
    > In your case you have 6 possible recipients. Make a temp array of 6
    > elements. Add the beginning of the Click() event initialize a counter
    > (recipientCount) to zero. Then check each of your checkboxes and add
    > email addresses as needed and increment the recipientCount (also your
    > array index) before examining the next checkbox. When you are done,
    > check your counter, if still 0, use the built-in MsgBox(strMsg as
    > string) function to popup a warning message. Otherwise create a new
    > array of the correct size (using Redim) and copy your email list over
    > to the new object.
    >
    > The warning message "Something is trying to send email from this app -
    > OK?" is to allow you to stop a potentially malicious macro from
    > sending email. I am not sure if this can be turned off, but if so, it
    > would probably be by setting Macro Security to Low
    > (Tools->Macros->Macro Security). You may also be able to set yourself
    > up as a trusted publisher (I am not familiar with that process).
    >
    >
    > If the sendMail function will work with a recipient string like
    > "[email protected]; [email protected]; [email protected]" (i.e.
    > semi-colon or comma separated list) you could avoid arrays and just
    > append addresses to your string.
    >
    > "Hogan's Goat" wrote:
    >
    >> Hi, sorry if I appear totally lame but I know just enough VBA to be
    >> highly dangerous. I am trying to make a small dialog that will do
    >> many things, including email the active workbook to user's choice of
    >> email addresses.
    >>
    >> I have checkboxes by each name which will correspond to a specific
    >> email address, plus one with a text box that will allow a user to
    >> supply an email address, like this:
    >>
    >> cbCustomer
    >> |-tbCustomerEmail
    >> cbSales
    >> cbAdmin
    >> cbMfg
    >> cbSupport
    >> cbMgmt
    >>
    >> What I want to do is first check to see if all of the checkboxes are
    >> not selected, and throw up a msgbox, then build an array based out of
    >> the selected checkboxes and add them into this line of code:
    >>
    >> ActiveWorkbook.SendMail Recipients:=arrayEmail,
    >> Subject:="Please
    >> process this order immediately"
    >> Application.Dialogs(xlDialogSendMail).Show
    >>
    >> My questions:
    >> 1 - How can I build the arrayEmail depending on which
    >> checkboxes are
    >> selected?
    >> 2 - Is that the best way to email the active workbook? It
    >> pops up
    >> the dialog about "Something is trying to send email from this app -
    >> OK?" which I would rather not have.
    >>
    >> Best regards,
    >> Me
    >>
    >>

    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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