quote:

---

Originally posted by: VicM
" Not as big as Barclays, but bigger than Sony, Ford, Volkswagon and lots of others!! "

---

Getting back to the performance review - after really good and really really good can we put in "Really Rock God!" (just for me) and umm ehum... bigger than Sony Vic? is that strictly true......?

Love ya!

Does the jackal have NT?

In any case, I'll try to crank it out for both. It'll give me an excuse to develop for the different platforms, something I've been meaning to do, right Mitchell (where are you man? )?

You tell me the event Suey, and we'll shut 'em down.

Can't you use worksheet protection on the individual sheets? Simply toggle the locked property under Format->Cells->Protection.

Also note, worksheet protection is a joke. In Excel '00, the following code will unprotect the last worksheet in the workbook:

Sub RemSheetPassword()
With Sheets(Sheets.Count)
   .Protect vbNullString, UserInterfaceOnly:=True
   .[a1].Copy .[a1]
End With
End Sub



Back to the code though. Are the functions that are being mucked with all in the same location on the individual sheets? Where should the code look, like an address?

Sounds good then Suey.

Okay. Here's some of the sound code.


Option Explicit

Private Const MMSYSERR_NOERROR = 0
Private Const MIXER_SHORT_NAME_CHARS = 16
Private Const MIXER_LONG_NAME_CHARS = 64
Private Const MAXPNAMELEN = 32
Private Const MAXERRORLENGTH = 128
Private Const MM_MIXM_LINE_CHANGE = &H3D0
Private Const MM_MIXM_CONTROL_CHANGE = &H3D1
Private Const MIXERR_BASE = 1024
Private Const MIXERR_INVALLINE = (MIXERR_BASE + 0)
Private Const MIXERR_INVALCONTROL = (MIXERR_BASE + 1)
Private Const MIXERR_INVALVALUE = (MIXERR_BASE + 2)
Private Const MIXERR_LASTERROR = (MIXERR_BASE + 2)
Private Const MIXER_OBJECTF_HANDLE = &H80000000
Private Const MIXER_OBJECTF_MIXER = &H0&
Private Const MIXER_OBJECTF_HMIXER = _
(MIXER_OBJECTF_HANDLE Or MIXER_OBJECTF_MIXER)
Private Const MIXER_OBJECTF_WAVEOUT = &H10000000
Private Const MIXER_OBJECTF_HWAVEOUT = _
(MIXER_OBJECTF_HANDLE Or MIXER_OBJECTF_WAVEOUT)
Private Const MIXER_OBJECTF_WAVEIN = &H20000000
Private Const MIXER_OBJECTF_HWAVEIN = _
(MIXER_OBJECTF_HANDLE Or MIXER_OBJECTF_WAVEIN)
Private Const MIXER_OBJECTF_MIDIOUT = &H30000000
Private Const MIXER_OBJECTF_HMIDIOUT = _
(MIXER_OBJECTF_HANDLE Or MIXER_OBJECTF_MIDIOUT)
Private Const MIXER_OBJECTF_MIDIIN = &H40000000
Private Const MIXER_OBJECTF_HMIDIIN = _
(MIXER_OBJECTF_HANDLE Or MIXER_OBJECTF_MIDIIN)
Private Const MIXER_OBJECTF_AUX = &H50000000
Private Const MIXERLINE_LINEF_ACTIVE = &H1&
Private Const MIXERLINE_LINEF_DISCONNECTED = &H8000&
Private Const MIXERLINE_LINEF_SOURCE = &H80000000
Private Const MIXERLINE_COMPONENTTYPE_DST_FIRST = &H0&
Private Const MIXERLINE_COMPONENTTYPE_DST_UNDEFINED = _
(MIXERLINE_COMPONENTTYPE_DST_FIRST + 0)
Private Const MIXERLINE_COMPONENTTYPE_DST_DIGITAL = _
(MIXERLINE_COMPONENTTYPE_DST_FIRST + 1)
Private Const MIXERLINE_COMPONENTTYPE_DST_LINE = _
(MIXERLINE_COMPONENTTYPE_DST_FIRST + 2)
Private Const MIXERLINE_COMPONENTTYPE_DST_MONITOR = _
(MIXERLINE_COMPONENTTYPE_DST_FIRST + 3)
Private Const MIXERLINE_COMPONENTTYPE_DST_SPEAKERS = _
(MIXERLINE_COMPONENTTYPE_DST_FIRST + 4)
Private Const MIXERLINE_COMPONENTTYPE_DST_HEADPHONES = _
(MIXERLINE_COMPONENTTYPE_DST_FIRST + 5)
Private Const MIXERLINE_COMPONENTTYPE_DST_TELEPHONE = _
(MIXERLINE_COMPONENTTYPE_DST_FIRST + 6)
Private Const MIXERLINE_COMPONENTTYPE_DST_WAVEIN = _
(MIXERLINE_COMPONENTTYPE_DST_FIRST + 7)

Private Const MIXERLINE_COMPONENTTYPE_SRC_FIRST = &H1000&
Private Const MIXERLINE_COMPONENTTYPE_SRC_LINE = _
(MIXERLINE_COMPONENTTYPE_SRC_FIRST + 2)
Private Const MIXERLINE_COMPONENTTYPE_SRC_MICROPHONE = _
(MIXERLINE_COMPONENTTYPE_SRC_FIRST + 3)
Private Const MIXERLINE_COMPONENTTYPE_SRC_SYNTHESIZER = _
(MIXERLINE_COMPONENTTYPE_SRC_FIRST + 4)
Private Const MIXERLINE_COMPONENTTYPE_SRC_COMPACTDISC = _
(MIXERLINE_COMPONENTTYPE_SRC_FIRST + 5)
Private Const MIXERLINE_COMPONENTTYPE_SRC_PCSPEAKER = _
(MIXERLINE_COMPONENTTYPE_SRC_FIRST + 7)
Private Const MIXERLINE_COMPONENTTYPE_SRC_WAVEOUT = _
(MIXERLINE_COMPONENTTYPE_SRC_FIRST + 8)
Private Const MIXERLINE_COMPONENTTYPE_SRC_AUXILIARY = _
(MIXERLINE_COMPONENTTYPE_SRC_FIRST + 9)
Private Const MIXERLINE_TARGETTYPE_UNDEFINED = 0
Private Const MIXERLINE_TARGETTYPE_WAVEOUT = 1
Private Const MIXERLINE_TARGETTYPE_WAVEIN = 2
Private Const MIXERLINE_TARGETTYPE_MIDIOUT = 3
Private Const MIXERLINE_TARGETTYPE_MIDIIN = 4
Private Const MIXERLINE_TARGETTYPE_AUX = 5
Private Const MIXER_GETLINEINFOF_COMPONENTTYPE = &H3&
Private Const MIXERCONTROL_CONTROLF_UNIFORM = &H1&
Private Const MIXERCONTROL_CONTROLF_MULTIPLE = &H2&
Private Const MIXERCONTROL_CONTROLF_DISABLED = &H80000000
Private Const MIXERCONTROL_CT_CLASS_SWITCH = &H20000000
Private Const MIXERCONTROL_CT_CLASS_FADER = &H50000000
Private Const MIXERCONTROL_CT_SC_SWITCH_BOOLEAN = &H0&
Private Const MIXERCONTROL_CT_UNITS_BOOLEAN = &H10000
Private Const MIXERCONTROL_CT_UNITS_UNSIGNED = &H30000
Private Const MIXERCONTROL_CONTROLTYPE_BOOLEAN = _
(MIXERCONTROL_CT_CLASS_SWITCH Or MIXERCONTROL_CT_SC_SWITCH_BOOLEAN _
Or MIXERCONTROL_CT_UNITS_BOOLEAN)
Private Const MIXERCONTROL_CONTROLTYPE_MUTE = _
(MIXERCONTROL_CONTROLTYPE_BOOLEAN + 2)
Private Const MIXERCONTROL_CONTROLTYPE_FADER = _
(MIXERCONTROL_CT_CLASS_FADER Or MIXERCONTROL_CT_UNITS_UNSIGNED)
Private Const MIXERCONTROL_CONTROLTYPE_VOLUME = _
(MIXERCONTROL_CONTROLTYPE_FADER + 1)
Private Const MIXER_GETLINECONTROLSF_ONEBYID = &H1&
Private Const MIXER_GETLINECONTROLSF_ONEBYTYPE = &H2&
Private Const MIXER_GETCONTROLDETAILSF_VALUE = &H0&
Private Const MIXER_SETCONTROLDETAILSF_VALUE = &H0&

Private Type Target
dwType As Long
dwDeviceID As Long
wMid As Integer
wPid As Integer
vDriverVersion As Long
szPname As String * MAXPNAMELEN
End Type

Private Type MIXERLINE
cbStruct As Long
dwDestination As Long
dwSource As Long
dwLineID As Long
fdwLine As Long
dwUser As Long
dwComponentType As Long
cChannels As Long
cConnections As Long
cControls As Long
szShortName As String * MIXER_SHORT_NAME_CHARS
szName As String * MIXER_LONG_NAME_CHARS
tTarget As Target
End Type

Private Type MIXERCONTROL
cbStruct As Long
dwControlID As Long
dwControlType As Long
fdwControl As Long
cMultipleItems As Long
szShortName(1 To MIXER_SHORT_NAME_CHARS) As Byte
szName(1 To MIXER_LONG_NAME_CHARS) As Byte
lMinimum As Long
lMaximum As Long
RESERVED(10) As Long
End Type

Private Type MIXERLINECONTROLS
cbStruct As Long
dwLineID As Long
dwControl As Long
cControls As Long
cbmxctrl As Long
pamxctrl As Long
End Type

Private Type MIXERCONTROLDETAILS
cbStruct As Long
dwControlID As Long
cChannels As Long
item As Long
cbDetails As Long
paDetails As Long
End Type

Private Type MIXERCONTROLDETAILS_UNSIGNED
dwValue As Long
End Type

Private Declare Function mixerGetNumDevs Lib "winmm.dll" () As Long
Private Declare Function mixerOpen Lib "winmm.dll" (phmx As Long, _
ByVal uMxId As Long, ByVal dwCallback As Long, _
ByVal dwInstance As Long, ByVal fdwOpen As Long) As Long
Private Declare Function mixerClose Lib "winmm.dll" (ByVal hmx As Long) As Long
Private Declare Function mixerGetLineInfo Lib "winmm.dll" _
Alias "mixerGetLineInfoA" (ByVal hmxobj As Long, pmxl As MIXERLINE, _
ByVal fdwInfo As Long) As Long
Private Declare Function mixerGetLineControls Lib "winmm.dll" _
Alias "mixerGetLineControlsA" (ByVal hmxobj As Long, pmxlc _
As MIXERLINECONTROLS, ByVal fdwControls As Long) As Long
Private Declare Function mixerGetControlDetails Lib "winmm.dll" Alias _
"mixerGetControlDetailsA" (ByVal hmxobj As Long, pmxcd As _
MIXERCONTROLDETAILS, ByVal fdwDetails As Long) As Long
Private Declare Function mixerSetControlDetails Lib "winmm.dll" _
(ByVal hmxobj As Long, pmxcd As MIXERCONTROLDETAILS, _
ByVal fdwDetails As Long) As Long
Private Declare Sub CopyStructFromPtr Lib "kernel32" Alias _
"RtlMoveMemory" (struct As Any, ByVal ptr As Long, ByVal cb As Long)
Private Declare Sub CopyPtrFromStruct Lib "kernel32" Alias _
"RtlMoveMemory" (ByVal ptr As Long, struct As Any, ByVal cb As Long)
Private Declare Function GlobalAlloc Lib "kernel32" ( _
ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function GlobalLock Lib "kernel32" ( _
ByVal hMem As Long) As Long
Private Declare Function GlobalFree Lib "kernel32" ( _
ByVal hMem As Long) As Long

Private uMixerControls(20) As MIXERCONTROL
Private hMixerHandle As Long

Public Enum VOL_CONTROL
Speaker = 0
LINEIN = 1
MICROPHONE = 2
SYNTHESIZER = 3
COMPACTDISC = 4
WAVEOUT = 5
AUXILIARY = 6
End Enum

Public Enum MUTE_CONTROL
SPEAKER_MUTE = 7
LINEIN_MUTE = 8
MICROPHONE_MUTE = 9
SYNTHESIZER_MUTE = 10
COMPACTDISC_MUTE = 11
WAVEOUT_MUTE = 12
AUXILIARY_MUTE = 13
End Enum

Public Function OpenMixer(ByVal MixerNumber As Long) As Long
Dim ret As Long
If MixerNumber < 0 Or MixerNumber > mixerGetNumDevs - 1 _
Then Exit Function
ret = mixerOpen(hMixerHandle, MixerNumber, 0, 0, 0)
If ret MMSYSERR_NOERROR Then Exit Function
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_DST_SPEAKERS, _
MIXERCONTROL_CONTROLTYPE_VOLUME, uMixerControls(Speaker))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_SRC_MICROPHONE, _
MIXERCONTROL_CONTROLTYPE_VOLUME, uMixerControls(MICROPHONE))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_SRC_AUXILIARY, _
MIXERCONTROL_CONTROLTYPE_VOLUME, uMixerControls(AUXILIARY))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_SRC_COMPACTDISC, _
MIXERCONTROL_CONTROLTYPE_VOLUME, uMixerControls(COMPACTDISC))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_SRC_SYNTHESIZER, _
MIXERCONTROL_CONTROLTYPE_VOLUME, uMixerControls(SYNTHESIZER))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_SRC_WAVEOUT, _
MIXERCONTROL_CONTROLTYPE_VOLUME, uMixerControls(WAVEOUT))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_SRC_LINE, _
MIXERCONTROL_CONTROLTYPE_VOLUME, uMixerControls(LINEIN))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_DST_SPEAKERS, _
MIXERCONTROL_CONTROLTYPE_MUTE, uMixerControls(SPEAKER_MUTE))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_SRC_MICROPHONE, _
MIXERCONTROL_CONTROLTYPE_MUTE, uMixerControls(MICROPHONE_MUTE))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_SRC_AUXILIARY, _
MIXERCONTROL_CONTROLTYPE_MUTE, uMixerControls(AUXILIARY_MUTE))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_SRC_COMPACTDISC, _
MIXERCONTROL_CONTROLTYPE_MUTE, uMixerControls(COMPACTDISC_MUTE))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_SRC_SYNTHESIZER, _
MIXERCONTROL_CONTROLTYPE_MUTE, uMixerControls(SYNTHESIZER_MUTE))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_SRC_WAVEOUT, _
MIXERCONTROL_CONTROLTYPE_MUTE, uMixerControls(WAVEOUT_MUTE))
ret = GetMixerControl(hMixerHandle, MIXERLINE_COMPONENTTYPE_SRC_LINE, _
MIXERCONTROL_CONTROLTYPE_MUTE, uMixerControls(LINEIN_MUTE))
OpenMixer = True
End Function

Public Function CloseMixer() As Long
CloseMixer = mixerClose(hMixerHandle)
hMixerHandle = 0
End Function

Public Function SetVolume(Control As VOL_CONTROL, ByVal NewVolume As Long) As Long
SetVolume = SetControlValue(hMixerHandle, uMixerControls(Control), NewVolume)
End Function

Public Function GetVolume(Control As VOL_CONTROL) As Long
GetVolume = GetControlValue(hMixerHandle, uMixerControls(Control))
End Function

Public Function SetMute(Control As MUTE_CONTROL, _
ByVal MuteState As Boolean) As Boolean
Dim Mute As Long
Mute = Abs(MuteState)
SetMute = SetControlValue(hMixerHandle, uMixerControls(Control), Mute)
End Function

Public Function GetMute(Control As MUTE_CONTROL) As Boolean
GetMute = CBool(-GetControlValue(hMixerHandle, uMixerControls(Control)))
End Function

Private Function GetMixerControl(ByVal hMixer As Long, ByVal componentType _
As Long, ByVal ctrlType As Long, ByRef mxc As MIXERCONTROL) As Long

Dim mxlc As MIXERLINECONTROLS
Dim mxl As MIXERLINE
Dim hMem As Long
Dim ret As Long

mxl.cbStruct = Len(mxl)
mxl.dwComponentType = componentType

ret = mixerGetLineInfo(hMixer, mxl, MIXER_GETLINEINFOF_COMPONENTTYPE)

If ret = MMSYSERR_NOERROR Then
mxlc.cbStruct = Len(mxlc)
mxlc.dwLineID = mxl.dwLineID
mxlc.dwControl = ctrlType
mxlc.cControls = 1
mxlc.cbmxctrl = Len(mxc)

hMem = GlobalAlloc(&H40, Len(mxc))
mxlc.pamxctrl = GlobalLock(hMem)
mxc.cbStruct = Len(mxc)

ret = mixerGetLineControls(hMixer, mxlc, MIXER_GETLINECONTROLSF_ONEBYTYPE)

If ret = MMSYSERR_NOERROR Then
GetMixerControl = True

CopyStructFromPtr mxc, mxlc.pamxctrl, Len(mxc)
Else
GetMixerControl = False
End If
GlobalFree (hMem)
Exit Function
End If

GetMixerControl = False

End Function

Private Function SetControlValue(ByVal hMixer As Long, _
mxc As MIXERCONTROL, ByVal NewVolume As Long) As Boolean
Dim mxcd As MIXERCONTROLDETAILS
Dim vol As MIXERCONTROLDETAILS_UNSIGNED
Dim hMem As Long
Dim ret As Long

mxcd.item = 0
mxcd.dwControlID = mxc.dwControlID
mxcd.cbStruct = Len(mxcd)
mxcd.cbDetails = Len(vol)

hMem = GlobalAlloc(&H40, Len(vol))
mxcd.paDetails = GlobalLock(hMem)
mxcd.cChannels = 1

If mxc.lMaximum > 100 Then
vol.dwValue = NewVolume * (mxc.lMaximum 100)
Else
vol.dwValue = NewVolume
End If
If vol.dwValue > mxc.lMaximum Then vol.dwValue = mxc.lMaximum
If vol.dwValue < mxc.lMinimum Then vol.dwValue = mxc.lMinimum

CopyPtrFromStruct mxcd.paDetails, vol, Len(vol)

ret = mixerSetControlDetails(hMixer, mxcd, MIXER_SETCONTROLDETAILSF_VALUE)
GlobalFree (hMem)

If ret = MMSYSERR_NOERROR Then SetControlValue = True

End Function

Private Function GetControlValue(ByVal hMixer As Long, mxc As MIXERCONTROL) _
As Long
'This function gets the value for a control.
Dim mxcd As MIXERCONTROLDETAILS
Dim vol As MIXERCONTROLDETAILS_UNSIGNED
Dim hMem As Long
Dim ret As Long

mxcd.item = 0
mxcd.dwControlID = mxc.dwControlID
mxcd.cbStruct = Len(mxcd)
mxcd.cbDetails = Len(vol)

hMem = GlobalAlloc(&H40, Len(vol))
mxcd.paDetails = GlobalLock(hMem)
mxcd.cChannels = 1

ret = mixerGetControlDetails(hMixer, mxcd, _
MIXER_GETCONTROLDETAILSF_VALUE)

CopyStructFromPtr vol, mxcd.paDetails, Len(vol)

If mxc.lMaximum > 100 Then
GetControlValue = (vol.dwValue * 100) / mxc.lMaximum - mxc.lMinimum
Else
GetControlValue = vol.dwValue
End If

GlobalFree (hMem)

End Function

Piece of pi$$ that Nucker.  No probs!