Like '.CommandBars("Cell")' in Page Break Preview

A

AH·C

Does anyone know how to add right-click (context) menus to a spreadsheet
while in Page Break Preview (PBP)? I'm using XL XP & XL 2003.

My dilemna is that most of my users are in the PBV and I need to be
able to present some right-click options, whether the cell is normal or
within a query field. As a result, I see I will have to code for 4
various scenarios.

To wit, CommandBars("Cell") works fine while in Normal View (NV).
While struggling to find a solution, I discovered that there are a
bunch of built-in pop-up commandbars. Some of which are view
dependent.

For example: CommandBars("Query Layout") works in lieu of 'Cell' when
the active cell is within a query view in PBP, yet CommandBars("Query")
does likewise, but only in NV.

Sigh, why CommandBars("Cell") can't work all the time, except when
disabled is beyond me -- must be a M$ feature

I've even created a macro to loop thru all of the available commandbars
to add a control to test & see which commandbar is at play -- nada as
far as PBP 'Cell' is concerned.

TIA
 
J

Jim Cone

AH·C,

What does "TIA" mean?

Jim Cone
San Francisco, USA



in message
Does anyone know how to add right-click (context) menus to a spreadsheet
while in Page Break Preview (PBP)? I'm using XL XP & XL 2003.

My dilemna is that most of my users are in the PBV and I need to be
able to present some right-click options, whether the cell is normal or
within a query field. As a result, I see I will have to code for 4
various scenarios.

To wit, CommandBars("Cell") works fine while in Normal View (NV).
While struggling to find a solution, I discovered that there are a
bunch of built-in pop-up commandbars. Some of which are view
dependent.

For example: CommandBars("Query Layout") works in lieu of 'Cell' when
the active cell is within a query view in PBP, yet CommandBars("Query")
does likewise, but only in NV.

Sigh, why CommandBars("Cell") can't work all the time, except when
disabled is beyond me -- must be a M$ feature

I've even created a macro to loop thru all of the available commandbars
to add a control to test & see which commandbar is at play -- nada as
far as PBP 'Cell' is concerned.

TIA
AH·C
 
A

AH·C

Sorry I missed your question, I had to step out for a while on an
errand. Yes, TIA means Thanks In Advance.
 
J

Jim Cone

Let's see if I'm right...

There are two "Cell" shortcut command bars.
Each one has a unique Index number.
MS in its' effort to avoid complete compatibility between Excel versions has
assigned different Index numbers to these command bars with each
version release...
xl97 21 & 24
xl2000 23 & 26
xl2002 28 & 31
xl2003 ?
The higher numbered indexes are the ones for PrintPreview.
You should be able to check the XL version and then use the correct Index
number or iterate thru all of them and verify that the command bar name is "Cell".

So if you are using xl20002, this could be what you wanted? ...

Sub WhichOne()
With Application.CommandBars(26).Controls.Add(Type:=msoControlButton)
.Caption = "Test This"
.OnAction = ThisWorkbook.Name & "!MyMacro"
End With
'-------------------------------

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



in message
Sorry I missed your question, I had to step out for a while on an
errand. Yes, TIA means Thanks In Advance.
 
G

George Carlin

i was once often found using a nifty little acronym that saved me a bunch of
time typing in chat rooms and made me look really cool like I was surfing
the computing cutting edge

it was

IWOOFUANLATSMABOTTICRAMMLRCLIWSTCCE

!
 
D

Dave Peterson

For xl2003, it's 33 and 36.

If you could trust excel to always have a difference of 3, you could use:

Option Explicit
Sub testme01()
Dim PBPCellIndex As Long
Dim NormalCellIndex As Long

NormalCellIndex = Application.CommandBars("Cell").Index
PBPCellIndex = NormalCellIndex + 3

End Sub

If you couldn't trust it, then maybe you could trust excel to keep the indices
less than a number (I used 60) and I assumed that the normal view index would be
a lower number:

Option Explicit
Sub testme02()
Dim PBPCellIndex As Long
Dim NormalCellIndex As Long
Dim iCtr As Long

For iCtr = 1 To 60
If LCase(Application.CommandBars(iCtr).Name) = "cell" Then
If NormalCellIndex = 0 Then
NormalCellIndex = iCtr
Else
PBPCellIndex = iCtr
Exit For
End If
End If
Next iCtr

If NormalCellIndex * PBPCellIndex = 0 Then
MsgBox "Both were not found"
Else
With Application.CommandBars
MsgBox NormalCellIndex & "-" & PBPCellIndex & vbLf & _
.Item(NormalCellIndex).Name & vbLf & _
.Item(PBPCellIndex).Name
End With
End If

End Sub

Then just use those indices to update the toolbars.
 
A

AH·C

Dave/Jim, thanks for the info on Index. I had noticed that there wer
two sets of 'cell', 'column' & 'row' and that the index issue was goin
to be the next thing to tackle.

For what it's worth, xl2003 is 37 & 40. And your suggestion fo
getting the index # is helpful as I can use this to make the cod
compatible with all XL versions. However, after recoding, I stil
can't get the added menu items to show up in either View, even tho th
message boxes seem to indicate that I'm getting the right values.

Here's what I have :

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVa
Target As Range, Cancel As Boolean)
Dim PBPCellIndex As Long
Dim NormalCellIndex As Long
Dim iCtr As Long

If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Not Application.Intersect(Target, Range("E15:E3000")) Is Nothin
Then
For iCtr = 1 To 60
If LCase(Application.CommandBars(iCtr).Name) = "cell" Then
If NormalCellIndex = 0 Then
NormalCellIndex = iCtr
With Application
.CommandBars.Item(NormalCellIndex).Controls("Cros
Reference This " & .Item(NormalCellIndex).Name & " Only").Delete
.CommandBars.Item(NormalCellIndex).Controls("Canno
Cross Reference This " & .Item(NormalCellIndex).Name).Delete

.CommandBars.Item(NormalCellIndex).Controls("").Delete
MsgBox ("Cross Reference This "
.Item(NormalCellIndex).Name & vbLf & _
"Cannot Cross Reference This "
.Item(NormalCellIndex).Name)
End With
With Application.CommandBars
MsgBox ("Cross Reference This "
.Item(NormalCellIndex).Name & vbLf & _
"Cannot Cross Reference This "
.Item(NormalCellIndex).Name)
End With
Else
PBPCellIndex = iCtr
With Application
.CommandBars.Item(PBPCellIndex).Controls("Cros
Reference This " & .Item(PBPCellIndex).Name & " Only").Delete
.CommandBars.Item(PBPCellIndex).Controls("Canno
Cross Reference This " & .Item(PBPCellIndex).Name).Delete

.CommandBars.Item(PBPCellIndex).Controls("").Delete
End With
With Application.CommandBars
MsgBox ("Cross Reference This "
.Item(PBPCellIndex).Name & vbLf & _
"Cannot Cross Reference This "
.Item(PBPCellIndex).Name)
End With
End If
End If
If Len(ActiveCell) = 9 And Left(ActiveCell, 2) <> "PN" The
'test for 9 *digits* and whether "PN" exists
'note:can't test for ISNUMBER, since the cell is TEX
formatted
With Application.Controls.Add(msoControlButton, , ,
True)
.Visible = True
.TooltipText = "Click this to run Cross Reference onl
on This" & .Item(NormalCellIndex).Name
.BeginGroup = True
.Caption = "Cross Reference This "
.Item(NormalCellIndex).Name & " Only"
.State = msoButtonUp
.Style = msoButtonIconAndCaption
.OnAction = "CrossReferenceActiveCellStockNumber"
SheetMaster.Shapes("goCrossReference").Copy
.PasteFace
End With
Else
With Application.Controls.Add(msoControlButton, , ,
True)
.Visible = True
.TooltipText = "This " & .Item(PBPCellIndex).Name &
is not a 9-digit Stock Number"
.BeginGroup = True
.Caption = "Cannot Cross Reference This "
.Item(PBPCellIndex).Name
.State = msoButtonUp
.Style = msoButtonIconAndCaption
.OnAction = "AcceptActiveCellNonStockNumber"
SheetMaster.Shapes("noCrossReference").Copy
.PasteFace
End With
Exit For
End If
Next iCtr
End If
End Sub

TIA
btw what's IWOOFUANLATSMABOTTICRAMMLRCLIWSTCCE :confused
 
A

AH·C

Jim/Dave, thanks so much for your assist. Right after I posted earlier
I figured out what was wrong. And now I have the solution I was lookin
for.

Keep in mind that E15:E3000 will typically be part of a query field.
The idea is that stock numbers are entered in this column. Then
query is run against a text(.txt) file to get price, stock locatio
etc.

Where my right-click menu comes in, is if the user finds out that on
of the stock numbers is invalid. That number can then be corrected, an
rather than run the entire query process, only that cell will b
re-queried.

Otherwise, if it is determined that there is no matching stock number
but that the part number is valid, then clicking on caption "Canno
CrossReference This ..." will force that value to be accepted an
permit manual input of price etc.

Alternatively, the user may decide to add another item, after havin
run the initial query, right-clicking will allow a query just for tha
active cell. Hence the need to show the menu in either Normal View o
in Page Break Preview and whether the cell is a normal cell or part o
a query field.

Right now, I am referencing the various command items as part of th
caption to verify which control is being activated. In real life, th
controls captions will either read "Cannot Cross Reference This Cell
or "Cross Reference This Cell Only", along with the applicable "go"
"no-go" icon.

Without further ado, here's my solution:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVa
Target As Range, Cancel As Boolean)
Dim PBPCellIndex As Long
Dim NormalCellIndex As Long
Dim iCtr As Long
Dim nNV As String
Dim nPBP As String

On Error Resume Next
With Application
.CommandBars("Query Layout").Controls("CrossReference This Quer
Layout Only").Delete
.CommandBars("Query Layout").Controls("Cannot CrossReference Thi
Query Layout").Delete
.CommandBars("Query Layout").Controls("").Delete
.CommandBars("Query").Controls("CrossReference This Quer
Only").Delete
.CommandBars("Query").Controls("Cannot CrossReference Thi
Query").Delete
.CommandBars("Query").Controls("").Delete
End With

If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
For iCtr = 1 To 60
If LCase(Application.CommandBars(iCtr).Name) = "cell" Then
If NormalCellIndex = 0 Then
NormalCellIndex = iCtr
With Application.CommandBars
nNV = .Item(NormalCellIndex).Name
'MsgBox ("CrossReference This " & nNV & vbLf & _
"Cannot CrossReference This " & nNV) 'fo
debugging
End With
With Application.CommandBars.Item(NormalCellIndex)
.Controls("CrossReference This nNV " & nNV &
Only").Delete
.Controls("Cannot CrossReference This nNV "
nNV).Delete
.Controls("").Delete
End With
Else
PBPCellIndex = iCtr
With Application.CommandBars.Item(PBPCellIndex)
nPBP = .Name
'MsgBox ("CrossReference This " & nPBP & vbLf & _
"Cannot CrossReference This " & nPBP) 'fo
debugging
.Controls("CrossReference This nPBP " & nPBP &
Only").Delete
.Controls("Cannot CrossReference This nPBP "
nPBP).Delete
.Controls("").Delete
End With
Exit For
End If
End If
Next iCtr
If Not Application.Intersect(Target, Range("E15:E3000")) Is Nothin
Then
If Len(ActiveCell) = 9 And Left(ActiveCell, 2) <> "PN" Then
Wit
Application.CommandBars.Item(NormalCellIndex).Controls.Add(msoControlButton
, , 5, True)
.Visible = True
.TooltipText = "Click this to run CrossReference onl
on This nNV " & nNV
.BeginGroup = True
.Caption = "CrossReference This nNV " & nNV & " Only"
.State = msoButtonUp
.Style = msoButtonIconAndCaption
.OnAction = "CrossReferenceActiveCell"
SheetMaster.Shapes("goCrossReference").Copy
.PasteFace
End With
Wit
Application.CommandBars.Item(PBPCellIndex).Controls.Add(msoControlButton
, , 5, True)
.Visible = True
.TooltipText = "Click this to run CrossReference onl
on This nPBP " & nPBP
.BeginGroup = True
.Caption = "CrossReference This nPBP " & nPBP &
Only"
.State = msoButtonUp
.Style = msoButtonIconAndCaption
.OnAction = "CrossReferenceActiveCell"
SheetMaster.Shapes("goCrossReference").Copy
.PasteFace
End With
With Application.CommandBars("Query
Layout").Controls.Add(msoControlButton, , , 5, True)
.Visible = True
.TooltipText = "Click this to run CrossReference only
on This Query Layout"
.BeginGroup = True
.Caption = "CrossReference This Query Layout Only"
.State = msoButtonUp
.Style = msoButtonIconAndCaption
.OnAction = "CrossReferenceActiveCell"
SheetMaster.Shapes("goCrossReference").Copy
.PasteFace
End With
With
Application.CommandBars("Query").Controls.Add(msoControlButton, , , 5,
True)
.Visible = True
.TooltipText = "Click this to run CrossReference only
on This Query"
.BeginGroup = True
.Caption = "CrossReference This Query Only"
.State = msoButtonUp
.Style = msoButtonIconAndCaption
.OnAction = "CrossReferenceActiveCell"
SheetMaster.Shapes("goCrossReference").Copy
.PasteFace
End With
Else
With
Application.CommandBars.Item(NormalCellIndex).Controls.Add(msoControlButton,
, , 6, True)
.Visible = True
.TooltipText = "This " & nNV & " is not a 9-digit Stock
Number"
.BeginGroup = True
.Caption = "Cannot CrossReference This nNV " & nNV
.State = msoButtonUp
.Style = msoButtonIconAndCaption
.OnAction = ""
SheetMaster.Shapes("noCrossReference").Copy
.PasteFace
End With
With
Application.CommandBars.Item(PBPCellIndex).Controls.Add(msoControlButton,
, , 6, True)
.Visible = True
.TooltipText = "This " & nPBP & " is not a 9-digit
Stock Number"
.BeginGroup = True
.Caption = "Cannot CrossReference This nPBP " & nPBP
.State = msoButtonUp
.Style = msoButtonIconAndCaption
.OnAction = ""
SheetMaster.Shapes("noCrossReference").Copy
.PasteFace
End With
With Application.CommandBars("Query
Layout").Controls.Add(msoControlButton, , , 6, True)
.Visible = True
.TooltipText = "This Query Layout is not a 9-digit
Stock Number"
.BeginGroup = True
.Caption = "Cannot CrossReference This Query Layout"
.State = msoButtonUp
.Style = msoButtonIconAndCaption
.OnAction = ""
SheetMaster.Shapes("noCrossReference").Copy
.PasteFace
End With
With
Application.CommandBars("Query").Controls.Add(msoControlButton, , , 6,
True)
.Visible = True
.TooltipText = "This Query is not a 9-digit Stock
Number"
.BeginGroup = True
.Caption = "Cannot CrossReference This Query"
.State = msoButtonUp
.Style = msoButtonIconAndCaption
.OnAction = ""
SheetMaster.Shapes("noCrossReference").Copy
.PasteFace
End With
End If
End If
End Sub

Thanks again for everything.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding Context Menu (right click) Options 5
Right Click Menu not working when sheet is in Page Break Preview m 7
Controlling Page Break Preview 7
Right click - Normal view/ Page break preview 0
Two Questions Page Break Preview 1
Custom run time CommandBar for navigation 0
Unwanted Sub Exit, Ws in page break preview View 0
Right Click menu doesn't appear in Page Break View 0

Top