Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Arcadia, California, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Where to insert VBA Code in Macro (Excel 2000)

    A really simple question from a really confused user. I'm trying to write a macro that
    a) changes the font color in the current cel from blue to white, and
    [img]/forums/images/smilies/cool.gif[/img] play a WAV file.

    I can get both to work in separate macros, but when I try to combine them, it doesn't work. The code is pasted below.
    -------------------------------------------------------
    Sub ChangeFontColor()
    '
    Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _
    (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

    Selection.Font.ColorIndex = 2
    Sub PlaySound()
    If Application.CanPlaySounds Then
    'Substitute the path and filename of the sound you want to play
    Call sndPlaySound32("c:windowsmediachimes.wav", 0)
    End If
    End Sub

    End Sub
    ------------------------------------------------------------------------------------------------------------

    Thanks for your help!

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

    Re: Where to insert VBA Code in Macro (Excel 2000)

    You cannot nest one procedure (Sub) within another. If you want to combine the code of two macros you must create ONE Sub ... End Sub pair. Moreover, the Declare part must come before all Subs and Functions in the module, it cannot be within a Sub or Function

    Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _
    (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

    Sub CombinedMacro()
    Selection.Font.ColorIndex = 2
    If Application.CanPlaySounds Then
    'Substitute the path and filename of the sound you want to play
    Call sndPlaySound32("c:windowsmediachimes.wav", 0)
    End If
    End Sub

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Arcadia, California, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where to insert VBA Code in Macro (Excel 2000)

    Thanks Hans! Works great! Once again you have saved me hours of frustration.

Posting Permissions

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