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]

Just used the recorder to get this:

Sheets("Lookups").Select
    Sheets.Add
    Sheets("Project Board Template").Select
    Cells.Select
    Selection.Copy
    Sheets("Sheet3").Select
    Cells.Select
    ActiveSheet.Paste
    Range("F5").Select
    Application.CutCopyMode = False
    Selection.ClearContents

So think I can put this in place of this code:

Copy Before:=Sheets("Lookups")

 

[Jay3]

So updated code now this:

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
    Sheets("Lookups").Select
    Sheets.Add
    Sheets("Project Board Template").Select
    Cells.Select
    Selection.Copy
    Sheets("Sheet3").Select
    Cells.Select
    ActiveSheet.Paste
    Range("F5").Select
    Application.CutCopyMode = False
    Selection.ClearContents
     With ActiveSheet
        .Range("B2").Value = wsName
        .Range("F2").Value = wsDescription
        .Range("F5").Value = ""
        .Name = wsName
      End With
     End If
     wsData.Select
End Sub

Just about to test.

 

[Jay3]

2 problems:

1) Sheets("Sheet3").Select

This line could go out of sync and scre things up

2) I'm not copying my macros over on the new sheet?

Might need to rethink this?

 

[RoryA]

Try something like this:

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
     With Sheets.Add
        Worksheets("Project Board Template").Cells.Copy .Range("A1")
        .Range("B2").Value = wsName
        .Range("F2").Value = wsDescription
        .Range("F5").Value = ""
        .Name = wsName
      End With
     End If
    
     wsData.Select
End Sub

 

[RoryA]

If you need to copy code, you're out of luck. I suggest you rethink the Shared Workbook approach altogether.

 

[Jay3]

I could create a number of template sheets. And then rename them on new project creation??

 

[Jay3]

Can I hide and unhide sheets in a shared workbook?

 

[RoryA]

Yes you can.

I'd still strongly recommend not using shared workbooks though.