The line that you stated is re-opening the workbook ISN'T!, the
workbook, if it meets criteria is ACTIVATED well before that, the fact
that you
Code:
--------------------
Set DatabaseWB = Workbooks.Open(......
--------------------
doesn't mean it that it is opening it, it is
simply setting a variable to be used instead of typing the whole thing
it would be used like
Code:
--------------------
Set DatabaseWB = Workbooks.Open(ThisWorkbook.Path & "\HC Price Lists.xlsm")
If Range("A1").Value = "Y" Then
DatabaseWB
End If
--------------------
If the workbook is indeed closed then you must trigger the reopen!
[color=#0000dd][/COLOR Wrote:
=ARbitOUR;461600]Hi there!
I have a macro in my 'Thisworkbook' object class that disables Cut/Copy
and 'drag-move', enabling it again after the workbook is closed. When I
manually open the workbook the macro automatically functions as it
should (i.e: graying out / disabling the Cut/Copy right-click menu
options as well as the Ctrl + X/V shortcuts...
After I have worked with the workbook (which acts as a main template)
it is saved under a different filename by a 'save-as' macro. After it is
saved one can re-open the original Workbook template with a 're-open'
macro. after executing the 're-open' macro, the original workbook (now
under a different file name) auto-saves itself, opens the original
workbook template again, then closes.
*For some reason, after executing the 're-open' macro, the 'disable cut
/ copy' macro in the 'Thisworkbook' object class does not do what it is
supposed to do: The Cut / Copy options in the right-click menu is
available again!!!!*
If I close this workbook template and manualy re-open it again, the
'Disable_Cut_Copy' macro again functions as it should.
I'm not sure if this problem is related to the 'Cut/Copy' macro or to
the 're-open' macro....
However....This template workbook contains a link to open another excel
workbook that also contains the exact same 'disable cut/copy' macro in
its 'Thisworkbook' object class...yet when it is opened via it's
specific macro it functions as it should, unlike when using the
're-open' macro to open the Template workbook.....
Here's the code I use in the 'Thisworkbook' object class for disabling
Cut/Copy etc. upon opening of the workbook:
Option Explicit
Sub EnableControl(Id As Integer, Enabled As Boolean)
Dim CB As CommandBar
Dim C As CommandBarControl
For Each CB In Application.CommandBars
Set C = CB.FindControl(Id:=Id, recursive:=True)
If Not C Is Nothing Then C.Enabled = Enabled
Next
End Sub
Private Sub Workbook_Activate()
EnableControl 21, False ' cut
EnableControl 19, False ' copy
EnableControl 22, False ' paste
EnableControl 755, False ' pastespecial
Application.OnKey "^c", ""
Application.OnKey "^v", ""
Application.OnKey "+{DEL}", ""
Application.OnKey "+{INSERT}", ""
Application.CellDragAndDrop = False
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
EnableControl 21, True ' cut
EnableControl 19, True ' copy
EnableControl 22, True ' paste
EnableControl 755, True ' pastespecial
Application.OnKey "^c"
Application.OnKey "^v"
Application.OnKey "+{DEL}"
Application.OnKey "+{INSERT}"
Application.CellDragAndDrop = True
End Sub
Private Sub Workbook_Deactivate()
EnableControl 21, True ' cut
EnableControl 19, True ' copy
EnableControl 22, True ' paste
EnableControl 755, True ' pastespecial
Application.OnKey "^c"
Application.OnKey "^v"
Application.OnKey "+{DEL}"
Application.OnKey "+{INSERT}"
Application.CellDragAndDrop = True
End Sub
Private Sub Workbook_Open()
EnableControl 21, False ' cut
EnableControl 19, False ' copy
EnableControl 22, False ' paste
EnableControl 755, False ' pastespecial
Application.OnKey "^c", ""
Application.OnKey "^v", ""
Application.OnKey "+{DEL}", ""
Application.OnKey "+{INSERT}", ""
Application.CellDragAndDrop = False
End Sub
And here's a sample of the code used to 're-open' the main template
workbook (when the 'cut/copy' macro doesn't function as it should).
(this code is also used to open the other workbook that contains the
'cut/copy' macro that functions as it should):
Public Function FileExists(FileName As String) As Boolean
Dim iTemp As Integer
On Error Resume Next
On Error Resume Next
iTemp = GetAttr(FileName)
Select Case Err.Number
Case Is = 0
FileExists = True
Case Else
FileExists = False
End Select
On Error GoTo 0
End Function
Private Sub CmdOpenPriceLists_Click()
Dim Confirm As String
Dim Answer As String
Dim DatabaseWB As Workbook
On Error Resume Next
Confirm = "Open price lists?"
Answer = MsgBox(Confirm, vbQuestion + vbYesNo, "OPEN PRICE LISTS")
If Answer = vbYes Then
If FileExists(ThisWorkbook.Path & "\HC Price Lists.xlsm") Then
OpenMsg1.Caption = "OPENING"
OpenMsg2.Caption = "...please wait..."
Set DatabaseWB = Workbooks(ThisWorkbook.Path & "\HC Price Lists.xlsm")
For Each DatabaseWB In Workbooks
If DatabaseWB.Name = "HC Price Lists.xlsm" Then
DatabaseWB.Activate
If ActiveWorkbook.ReadOnly = True Then
ActiveWorkbook.Close SaveChanges:=False
Else
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=True
Application.DisplayAlerts = True
End If
Else
End If
Next
Set DatabaseWB = Nothing
Set DatabaseWB = Workbooks.Open(ThisWorkbook.Path & "\HC Price
Lists.xlsm")
If ActiveWorkbook.ReadOnly = True Then
ActiveWorkbook.Saved = True
Else
ActiveWorkbook.Saved = False
End If
OpenMsg1.Caption = ""
OpenMsg2.Caption = ""
Set DatabaseWB = Nothing
End Sub
The actual line opening the workbook is:
Set DatabaseWB = Workbooks.Open(ThisWorkbook.Path &
"\HC Price Lists.xlsm")
...However I don't think this line is the culprit...!?
This problem is driving me insane 
....sigh....
Any help out there? aha!
--
Simon Lloyd
Regards,
Simon Lloyd
'Microsoft Office Help' (
http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=127677