ExcelBanter - Screen Res change macro leaves me in VB Editor instead of work

KeepITCool,

I tried both versions - as you pointed out, the shorter one left the applet
behind the window, but with the longer solution, I STILL end up in the VB
editor!

Any more thoughts?

My worksheet is called "Database", here's what I have at the moment...

Cheers

Pete

Option Explicit

Private Declare Function GetSystemMetrics Lib "user32.dll" (ByVal nIndex As
Long) As Long
Const SM_CXSCREEN = 0
Const SM_CYSCREEN = 1

Sub ScreenResolutionSub()
Dim x As Long, y As Long, sYourMessage, iConfirm As Integer

x = GetSystemMetrics(SM_CXSCREEN)
y = GetSystemMetrics(SM_CYSCREEN)

sYourMessage = "Your current screen size is " & x & " X " & y & vbCrLf & _
"Would you like to change the resolution?"

iConfirm = MsgBox(sYourMessage, vbExclamation + vbYesNo, "Screen
Resolution")
If iConfirm = vbYes Then

'------------------------------------------------------------------------------
'2nd fix version
Dim dHandle#
dHandle = Shell("rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,3")
AppActivate Application.Caption, True
Sheets("Database").Activate
AppActivate dHandle, False

'------------------------------------------------------------------------------
'1st fix
'Call Shell("rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,3")
'AppActivate Application.Caption

'------------------------------------------------------------------------------
End If
End Sub

"keepITcool" wrote:

use
AppActivate Application.Caption

to get back to excel window.
problem may be that the cpl applet is then hidden
behind the excel window... :)

this should work:

If iConfirm = vbYes Then
Dim dHandle#
dHandle = Shell( _
"rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,3")
AppActivate Application.Caption, True
Sheets(1).Activate
AppActivate dHandle, False
End If

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

Peter Rooney wrote :

Good morning all!

I'm running the following code, to allow users to change the screen
resolution.
----------------------------------------------------------------------
---------------------- Option Explicit

Private Declare Function GetSystemMetrics Lib "user32.dll" ( ByVal
nIndex As Long) _As Long
Const SM_CXSCREEN = 0
Const SM_CYSCREEN = 1
Sub ScreenResolutionSub()
Dim x As Long, y As Long, sYourMessage, iConfirm As Integer
x = GetSystemMetrics(SM_CXSCREEN)
y = GetSystemMetrics(SM_CYSCREEN)
sYourMessage = "Your current screen size is " & x & " X " & y &
vbCrLf & _ "Would you like to change the
resolution?" iConfirm = MsgBox(sYourMessage, vbExclamation +
vbYesNo, "Screen Resolution")
If iConfirm = vbYes Then
Call Shell("rundll32.exe shell32.dll,Control_RunDLL
desk.cpl,,3") Sheets("Database").Activate
End If
End Sub
----------------------------------------------------------------------
---------------------- It works fine (I didn't write it, so I can't
claim the credit), but I end up with the the VB editor screen
displayed, even though I'm in a worksheet when I run the macro.
Can anyone tell me the VBA to go back into Excel (i.e the equivalent
of Alt-F11) ?

Thanks in advance

Pete