This command is not available in a shared workbook

[Jay3]

Morning All,

Thought I had all my macros nailed but when I shared the workbook I got this error "This command is not available on a shared workbook".

The Macro was triggered upon selecting "Yes" from a data validation list and this is the macro.

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue As String
  Dim NewValue As String

  ' don't trigger any events if user is making some sort of change to multiple
  ' rows or columns simultaneously
  If Target.Cells.Count > 1 Then Exit Sub
 
  If Target.Column = 1 Then ThisWorkbook.Save
 
  If Not Intersect(Target, Columns("F")) Is Nothing Then
    If Target.Value = "YES" Then
      Call Create_Project_Board(Target.Row)
      ThisWorkbook.Save
    End If
  End If

It calls another macro but think the problem lies with this script although can't be 100% certain.

Any advice please?

Thanks =D

 

[Jay3]

Update: So I added a message box after

If Target.Column = 1 Then ThisWorkbook.Save

and the message box executed. So it must be related to the macro it's calling?

Which is this one:

Option Explicit
 
Sub Create_Project_Board(ByVal aRow As Long)
'if Yes is selected in column F check if a board has already been created with the corresponding project name. If yes then do nothing if not then create
'To create the board copy the tab called Project Template Board and rename the tab to the corresponding project name in cell A.
'Once done update project title and assigned to fields in c2 and c3 and also the project description in F by copying this information from the New Project
'Requiring Board tab

Dim wsName As String
Dim wsDescription As String
Dim wsData As Worksheet

Set wsData = ThisWorkbook.ActiveSheet

wsName = wsData.Range("A" & aRow).Value
wsDescription = wsData.Range("B" & aRow).Value

 If Not Evaluate("ISREF('" & wsName & "'!A1)") Then
    Worksheets("Project Board Template").Copy Before:=Sheets("Lookups")
     With ActiveSheet
        .Range("B2").Value = wsName
        .Range("F2").Value = wsDescription
        .Range("F5").Value = ""
        .Name = wsName
        .Tab.Color = 65280
      End With
     End If
    
     wsData.Select
End Sub

Any ideas?

 

[RoryA]

Among other things, you can't change tab colours in a Shared Workbook

 

[Jay3]

I've pinpointed the line.

This is where it falls over.

 Worksheets("Project Board Template").Copy Before:=Sheets("Lookups")

If Not Evaluate("ISREF('" & wsName & "'!A1)") Then
    Worksheets("Project Board Template").Copy Before:=Sheets("Lookups")
    MsgBox "Done:  It got to here before failing" & Space(10), _
         vbOKOnly + vbInformation, "Webteam Deliverables"
     With ActiveSheet
        .Range("B2").Value = wsName
        .Range("F2").Value = wsDescription
        .Range("F5").Value = ""
        .Name = wsName
        .Tab.Color = 65280
      End With
     End If
    
     wsData.Select
End Sub

 

[Jay3]

Thanks Rory, I'll remove the color change line. Do you know why this line would cause issues?

Worksheets("Project Board Template").Copy Before:=Sheets("Lookups")

 

[Jay3]

Oh no, am I not able to copy an existing worksheet in a shared workbook? That would really mess things up?

 

[RoryA]

Sorry, missed that line. No, you can't do that either. (Shared Workbooks are an abomination in Excel frankly)

 

[Jay3]

Sorry, missed that line. No, you can't do that either. (Shared Workbooks are an abomination in Excel frankly)

lol

Ok so I need to change my macro to create the new sheet and copy and paste all the contents from the template sheet and then rename it?

 

[RoryA]

Yes. And make lots and lots of backups of your file...

 

[Jay3]

Can you help me tweak this code to get it to do that?

Option Explicit
 
Sub Create_Project_Board(ByVal aRow As Long)
'if Yes is selected in column F check if a board has already been created with the corresponding project name. If yes then do nothing if not then create
'To create the board copy the tab called Project Template Board and rename the tab to the corresponding project name in cell A.
'Once done update project title and assigned to fields in c2 and c3 and also the project description in F by copying this information from the New Project
'Requiring Board tab



Dim wsName As String
Dim wsDescription As String
Dim wsData As Worksheet

Set wsData = ThisWorkbook.ActiveSheet


wsName = wsData.Range("A" & aRow).Value
wsDescription = wsData.Range("B" & aRow).Value



 If Not Evaluate("ISREF('" & wsName & "'!A1)") Then
    Worksheets("Project Board Template").Copy Before:=Sheets("Lookups")
     With ActiveSheet
        .Range("B2").Value = wsName
        .Range("F2").Value = wsDescription
        .Range("F5").Value = ""
        .Name = wsName
        .Tab.Color = 65280
      End With
     End If
    
     wsData.Select
End Sub