View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
JMay JMay is offline
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