Locking a sheet & Menu Items

[quietmalu]

Hi ~

Does anyone know how to lock an excel sheet using VBA? I have an excel
sheet that is set perfectly according to my coordinates. It loops
through various sections of the sheets, giving it the impression that
you're scrolling through pages (much like web pages). But if I resize
the sheet in anyway, the display gets messed up and not pretty.

2ndly, I want to create my own menu in Excel. Right now, I've created
a sheet of all the captions and actions that the menu items will do.
I'm running into a problem when it comes to submenu items. For
example, my menu items are menu1, menu2, menu3, menu4; my submenu items
are submenu1, submenu2. Menu2 and Menu4 have these submenus and the
other 2 doesn't. I've created a "for" loop to go through each of those
cells, but when it comes to the submenu items, an error gets displayed.

Can anyone help me out with this? Thanks in advance.

Denny.

 

[Phantom]

Ok, here's a bit more info. I'm creating a custom menu and this is a
brief version of my code. I know that Case 2 - 5 is very redundant
code, but I'm not sure how to condense it more.

Also, I'm not sure if this is even possible, but I'm trying to create a
conditional column in my spreadsheet. This column would show me a list
of parameters for a function to check against. For example,
conditional column says (a,b). Since there is 2 parameters, the
program would get the delimiter count and go to the appropriate
function based on the number of parameters, because the parameters
could increase or decrease based on a particular function. Therefore,
"a" and "b" would be passed to the function that accepts the 2
parameters and validates them and returns true or false. I could then
proceed to the next row and condition. Thanks for your input.


Here's my code:
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrAction = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
Conditional = .Cells(Row, 6)
End With

Select Case MenuLevel
Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrAction, _
Temporary:=True)
MenuObject.Caption = Caption

Case 2 ' A Menu Item
If Conditional <> "" Then
If Not (WizardCondition) Then
Set MenuItem = _

MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrAction
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
End If
Else
If NextLevel = 3 Then
Set MenuItem = _

MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem = _

MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrAction
End If
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True
End If

Case 3 ' A SubMenuLevel1 Item
If NextLevel = 4 Then
Set SubMenuLev1Item = _
MenuItem.Controls.Add(Type:=msoControlPopup)
Else
Set SubMenuLev1Item = _
MenuItem.Controls.Add(Type:=msoControlButton)
'SubMenuLev1Item.OnAction = PositionOrAction
End If
SubMenuLev1Item.Caption = Caption
If FaceId <> "" Then SubMenuLev1Item.FaceId = FaceId
If Divider Then SubMenuLev1Item.BeginGroup = True

Case 4 ' A SubMenuLevel2 Item
If NextLevel = 5 Then
Set SubMenuLev2Item = _

SubMenuLev1Item.Controls.Add(Type:=msoControlPopup)
Else
Set SubMenuLev2Item = _

SubMenuLev1Item.Controls.Add(Type:=msoControlButton)
'SubMenuLev2Item.OnAction = PositionOrAction
End If
SubMenuLev2Item.Caption = Caption
If FaceId <> "" Then SubMenuLev2Item.FaceId = FaceId
If Divider Then SubMenuLev2Item.BeginGroup = True

Case 5 ' A SubMenuLevel3 Item
Set SubMenuLev3Item = _

SubMenuLev2Item.Controls.Add(Type:=msoControlButton)
SubMenuLev3Item.Caption = Caption
'SubMenuLev3Item.OnAction = PositionOrAction
If FaceId <> "" Then SubMenuLev3Item.FaceId = FaceId
If Divider Then SubMenuLev3Item.BeginGroup = True
End Select
Row = Row + 1
Loop