Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Need explaining on 2 msgbox options. (Excel VBA)

    What does vbMsgBoxSetForeground + vbSystemModal do in an Excel msgbox? Help does not explain in detail.

    1. I know that modal forces the user to have to repond to the msgbox before they have access to the interface again. But I can see no difference whether the modal is on or off! If I do not use modal then the msgbox is still "modal" anyways???

    2. I have experimented with SetForground, and cannot see what its purpose is?

    TIA
    Regards,
    Rudi

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

    Re: Need explaining on 2 msgbox options. (Excel VBA)

    vbSystemModal keeps the message box in the foreground even if you switch to another application. In recent Windows versions, it doesn't prevent you from working in that other application, however.

    I think vbMsgBoxSetForeground is intended for use in Automation (controlling Excel in VBA from another application); I'm not 100% sure about this.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need explaining on 2 msgbox options. (Excel VBA)

    I tried it by starting Excel from Word and then showing a msgbox with this setting, but it remains tied to the Word window.

    If I recall correctly, these options don't "take" for VBA, they belong to VB6, in which they seem to have an effect.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need explaining on 2 msgbox options. (Excel VBA)

    I recall a Application Modal option on the msgbox too. Maybe that will prevent you from working in that other application. Tx for the reply.
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need explaining on 2 msgbox options. (Excel VBA)

    Hi Jan,
    OK, so it is for use in VB only then. This is OK...I don't use it anyways! Could you tell me (if you know) what its purpose is. Its just out of curiosity though!
    Tx
    Regards,
    Rudi

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

    Re: Need explaining on 2 msgbox options. (Excel VBA)

    vbApplicationModal has the value 0, so it is used by default. There is actually no reason to use this constant explicitly. A message box is always modal in the application it is called in, i.e. while the message box is displayed, the user cannot do anything in the application, and code execution is halted.

    vbSystemModal prevented the user from working in other applications in 16-bit versions of Windows (Windows 3.11 and earlier). Microsoft decided that it wasn't acceptable that one application could suspend all others on a truly multitasking system, so in 32 bit versions (Windows 95, Windows NT and later) it only keeps the message box in the foreground.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need explaining on 2 msgbox options. (Excel VBA)

    Tx. This has clarified the Modal issue for this thread. Cheers Hans.

    Now I will see if any comments come through for the msgboxSetForeground option! In the mean time I googled and came up with this MS Page that shows an example of this argument. I still am unclear to the context of it though???
    See Sample Code.

    Another observation: When setting up a msgbox I always used msgbox "Prompt", vbOKCancel + vbInformation + vbBlahBlah, "Title", ..... (I use the + sign) BUT I see this sample code and other code I saw uses the OR operator.
    I see now difference in the result! I assume theirfore that either can be used?

    Tx
    Regards,
    Rudi

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

    Re: Need explaining on 2 msgbox options. (Excel VBA)

    1) The sample code you refer to is meant for a VB6 application. Jan Karel Pieterse already indicated that vbMsgBoxSetForeground is only useful in VB6, not in VBA. This, and all other examples I've seen are used when automating another applucation (Excel in this example) from VB6.

    2) If you write out the message box constants in binary, you'll see why

    vbYesNo = decimal 4 = binary 00000100
    vbQuestion = decimal 32 = binary 00100000

    As you see, the 1s are in different positions. Hence the binary Or (which has 1 where either of the arguments has a 1) and the arithmetic + have the same result.

    Binary (00000100 Or 00100000) = binary 00100100 = decimal 36.
    Binary (00000100 + 00100000) = binary 00100100 = decimal 36.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need explaining on 2 msgbox options. (Excel VBA)

    Wow Hans, you could have simply said they are the same. But tx for the indepth explanation. I do appreciate it!
    Cheers
    Regards,
    Rudi

Posting Permissions

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