VBA code not working on Shared workbook

Question

I added some VBA code to a workbook. One code to hide a page header, another code to add a combo box to make dropdown font larger. Both VBA codes work beautifully when the workbook is not shared. When I share the workbook though, only the code for hiding the header works, The combo box code will not work in shared mode. If part of the VBA code works then this means the VBA codes are not restricted ina shared workbook. I just can't figure out why the rest of the code doesn't work? Any ideas. Here's the code (The bolded part  is working, the rest is not when workbook shared):

Private Sub ToggleButton1_Click()If ToggleButton1.Caption <> "Display Header (Push)" ThenToggleButton1.Caption = "Display Header (Push)"Rows("2:9").SelectSelection.EntireRow.Hidden = TrueRange("D12").SelectElseToggleButton1.Caption = "Hide Header (Push)"Rows("2:9").SelectSelection.EntireRow.Hidden = FalseRange("D12").SelectEnd IfEnd SubPrivate Sub MaterialCombo_KeyDown(ByVal _

        KeyCode As MSForms.ReturnInteger, _

        ByVal Shift As Integer)

    'Hide combo box and move to next cell on Enter and Tab

    Select Case KeyCode

        Case 9

            ActiveCell.Offset(0, 1).Activate

        Case 13

            ActiveCell.Offset(1, 0).Activate

        Case Else

            'do nothing

    End Select

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim str As String

Dim cboTemp As OLEObject

Dim ws As Worksheet

Dim wsList As Worksheet

Set ws = ActiveSheet

Set wsList = Sheets("Material")

Cancel = True

Set cboTemp = ws.OLEObjects("MaterialCombo")

  On Error Resume Next

  With cboTemp

    .ListFillRange = ""

    .LinkedCell = ""

    .Visible = False

  End With

On Error GoTo errHandler

  If Target.Validation.Type = 3 Then

    Application.EnableEvents = False

    str = Target.Validation.Formula1

    str = Right(str, Len(str) - 1)

    With cboTemp

      .Visible = True

      .Left = Target.Left

      .Top = Target.Top

      .Width = Target.Width + 15

      .Height = Target.Height + 5

      .ListFillRange = str

      .LinkedCell = Target.Address

    End With

    cboTemp.Activate

  End If

errHandler:

  Application.EnableEvents = True

  Exit Sub

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim str As String

Dim cboTemp As OLEObject

Dim ws As Worksheet

Set ws = ActiveSheet

Application.EnableEvents = False

Application.ScreenUpdating = False

If Application.CutCopyMode Then

  'allows copying and pasting on the worksheet

  GoTo errHandler

End If

Set cboTemp = ws.OLEObjects("MaterialCombo")

  On Error Resume Next

  With cboTemp

    .Top = 10

    .Left = 10

    .Width = 0

    .ListFillRange = ""

    .LinkedCell = ""

    .Visible = False

    .Value = ""

  End With

errHandler:

  Application.ScreenUpdating = True

  Application.EnableEvents = True

  Exit Sub

End Sub

Answer 1

Hi

Just wanted to check if there is a way to Filter a protected/shared workbook??

  ActiveSheet.Range("$A$1:$P$6000").AutoFilter Field:=4, Criteria1:="=MA1*", _

        Operator:=xlOr, Criteria2:="=MA2*"

This syntax will cause an error since the worksheet is protected which cant be helped since the workbook is shared.

Answer 2

"If part of the VBA code works then this means the VBA codes are not restricted ina shared workbook."

No, it means that only part of the code will work when the workbook is shared.

See http://office.microsoft.com/en-us/excel-help/features-that-are-unavailable-in-shared-workbooks-HP005201080.aspx for more information.