Screen Res change macro leaves me in VB Editor instead of workshee

[Peter Rooney]

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 Su
--------------------------------------------------------------------------------------------
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

 

[keepITcool]

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 :

 

[Peter Rooney]

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]

I think users can manager their own screen res, so i would never use
your code..it was a quick fix. i dont want to really think about this.

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


Peter Rooney wrote :

 

[Peter Rooney]

I guess you're probably right..! I only really needed this to help the
user check that the code to alter the screen zoom depending on the active
resolution when the workbook was opened, actually wored - and it does!

Thanks for your help - sorry about only replying now - I was on leave on
Friday afternoon!

Regards

Pete