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

My 3rd Msgbox is popping up in wrong position
 

Hi Jim,

To postion a MsgBox see Jim Rech's suggestion:

MsgBox Position
http://tinyurl.com/2xwsa4

However, perhaps consider using the InputBox function
which allows the dialog to be postioned.

As a matter of interesrt, why do you exclude a userform?


---
Regards,
Norman




"JMay" wrote in message
...
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

My 3rd Msgbox is popping up in wrong position
 

Norman;
Thanks!!

But first -- all this code "SCARES ME TO DEATH" !!

I copied and pasted into my sample file, and afterwards,
it didn't work. Is there an order in which all this code (of Jim Rech's code)
is to be arranged into the code window?

Jim



"Norman Jones" wrote:

Hi Jim,

To postion a MsgBox see Jim Rech's suggestion:

MsgBox Position
http://tinyurl.com/2xwsa4

However, perhaps consider using the InputBox function
which allows the dialog to be postioned.

As a matter of interesrt, why do you exclude a userform?


---
Regards,
Norman




"JMay" wrote in message
...
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

My 3rd Msgbox is popping up in wrong position
 

Hi Jim,

'-----------------
'But first -- all this code "SCARES ME TO DEATH" !!

I copied and pasted into my sample file, and afterwards,
it didn't work. Is there an order in which all this code (of Jim Rech's
code)
is to be arranged into the code window?
-----------------


The code works for me.

Try pasting Jim's code at the top of a standard module,
before any other code, and run the code from the Excel
interface.

I reported the link ti Jim's code primarily to show that it is
possible to position a MsgBox,

However, perhaps you might try the other suggestions.


---
Regards,
Norman

My 3rd Msgbox is popping up in wrong position
 

Yeah,

I think I'm suddenly interested in using a userform. Appreciate your help.
Jim

"Norman Jones" wrote:

Hi Jim,

'-----------------
'But first -- all this code "SCARES ME TO DEATH" !!

I copied and pasted into my sample file, and afterwards,
it didn't work. Is there an order in which all this code (of Jim Rech's
code)
is to be arranged into the code window?
-----------------


The code works for me.

Try pasting Jim's code at the top of a standard module,
before any other code, and run the code from the Excel
interface.

I reported the link ti Jim's code primarily to show that it is
possible to position a MsgBox,

However, perhaps you might try the other suggestions.


---
Regards,
Norman