LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 468
Default My 3rd Msgbox is popping up in wrong position

Sadly, I've just learned that one cannot postion a msgbox (non-Center) and
maybe a msgbox is not what I need (in the 3rd instance only - in code below).
I put the 3rd msgbox into my code so that I could **pause** the code that a
user could review the screen data and then "CLICK-ON_SOMETHING" to move to
the next sheet.

All my sheet have the Freeze Pane in effect so my code line GoHome is a
sub-routine which returns the cursor placement back to the position of
(CTR+HOME) before leaving the sheet and moving to the next one.

The only problem I've currently got is the 3rd Msgbox is "BLOCKING-THE-VIEW"
of my data in the Cell Reference I request. Can someone offer me an
alternative ohter than creating a Userform (in the MB's place)?

My Code:

Sub JumpToCell()
'shortcut key Ctrl + j
Dim CurrDayNum As Integer
Dim StartSheetIdxNum As Integer
Dim NumSheetsToEnd As Integer
Dim mcell As String
If Left(ActiveSheet.Name, 3) < "Day" Then
MsgBox "You must be on a sheetname beginning with ""Day"""
Exit Sub
End If
If Len(ActiveSheet.Name) = 4 Then
CurrDayNum = Right(ActiveSheet.Name, 1)
Else: CurrDayNum = Right(ActiveSheet.Name, 2)
End If
StartSheetIdxNum = ActiveSheet.Index
NumSheetsToEnd = StartSheetIdxNum + (31 - CurrDayNum)
ans = MsgBox("Are you currently on the sheet you wish to begin your
review?", vbYesNoCancel + vbQuestion, "Which sheet to begin review")
If ans = vbNo Or ans = vbCancel Then Exit Sub
mcell = Application.InputBox("Enter Cell Address you want to Goto")
For i = StartSheetIdxNum To NumSheetsToEnd
Sheets(i).Activate
Application.Goto Reference:=Range(mcell), Scroll:=True
fans = MsgBox("Ready to review next sheet?", vbOKOnly + vbQuestion)
GoHome ' Sub-routine not shown here but acts as a Ctrl+Home combo
Next i
Sheets(CurrDayNum).Activate
End Sub
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
MsgBox Position Dewald Excel Programming 2 October 14th 05 01:11 PM
MsgBox keeps popping up Rob[_17_] Excel Programming 1 January 25th 05 03:49 AM
MsgBox position RJH Excel Programming 2 August 19th 04 08:49 AM
Eliminating Excel msgbox from popping up while macro is running. dpdutke Excel Programming 2 November 23rd 03 07:55 PM
Position of MsgBox Konrad[_2_] Excel Programming 0 August 7th 03 05:27 PM


All times are GMT +1. The time now is 01:18 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"