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!


--
ARbitOUR
------------------------------------------------------------------------
ARbitOUR's Profile: http://www.thecodecage.com/forumz/member.php?userid=254
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127677

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

Simon Lloyd;461616 Wrote:
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!

Hi Simon,

First of, thx for the response.

you concluded your reply with "If the workbook is indeed closed then
you must trigger the reopen!"

The following code checks to see if it is already open...if it is open
it is activated, if it isn't, then is opens the workbook....


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


You will notice that the code tests whether the workbook in the sample
code is already open, if it is, then it is activated, if it isn't then
it is opened. There are NO other code in the macro to open the indicated
workbook only the line that you say does not open the workbook.....as
said yourself, it is a conditional opening of the workbook...(why open
it if it is already open right?)

either way, in hindsight I see that the sample code I posted was
actually the code where the 'cut/copy' macro works as it should (the
sample code is linked to a button control, when pressed, it opens the
price list database 'HC Price Lists' as it should (i.e with cut/copy
disabled).....So I accidentally posted the code that doesn't have the
problem. The code with the problem (i.e where the 'cut/copy' macro
doesn't function when 're-opening' via a macro - yet functioning
correctly when opened manualy) are as follows:

Private Sub SAMPLECODE_Click()

Dim NewQuote As Workbook
Set NewQuote = Workbooks.Open(ThisWorkbook.Path & "Quote.xlsm")

End Sub

EXACTLY THE SAME METHOD AS IN THE SAMPLE CODE WHERE IT WORKS! (only
difference is the workbook names aren't the same)

The code above opens the file 'Quote.xlsm' which ASLO contains the
'disable cut/copy' macro...yet in this case it doesn't work (cut/copy
isn't disabled when the above code is triggered).

Yet, when the first set of sample code is triggered (opening a
different workbook...the price list database...) the 'disable cut/copy'
code DOES execute....!!!???

Oh..the torture and misery!

PLEASE HELP?


--
ARbitOUR
------------------------------------------------------------------------
ARbitOUR's Profile: http://www.thecodecage.com/forumz/member.php?userid=254
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127677

Shouldn't this:ARbitOUR;461933 Wrote:
Private Sub SAMPLECODE_Click()

Dim NewQuote As Workbook
Set NewQuote = Workbooks.Open(ThisWorkbook.Path & "Quote.xlsm")

End SubBe:Private Sub SAMPLECODE_Click()

Dim NewQuote As Workbook
Set NewQuote = Workbooks.Open(ThisWorkbook.Path & "\Quote.xlsm")

End Sub


--
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

Simon Lloyd;461934 Wrote:
Shouldn't this:Be:Private Sub SAMPLECODE_Click()

Dim NewQuote As Workbook
Set NewQuote = Workbooks.Open(ThisWorkbook.Path & "\Quote.xlsm")

End Sub

You're right Simon...Type-O when I posted...it reads with a backslash
in the code so that's not the problem...

Thx anyways though.

I've also tried using 'n timed delay after opening the the workbook to
allow excel sufficient time to execute all the instructions in the
'disable cut/copy' macro...didn't help ('disable Copy/paste' still
didn't execute)... However, I noticed that when the file has been
re-opened (after it was saved) the 'disable cut/copy' macro doesn't
execute (as I have explained previosly)

...BUT....

When I run the save macro (I.e saving the main template - Quote.xlsm -
with a diff. filename)...then suddenly the 'disable cut/copy' macro has
executed....weird that...I'l re-check all the code in the save macro...

sigh


--
ARbitOUR
------------------------------------------------------------------------
ARbitOUR's Profile: http://www.thecodecage.com/forumz/member.php?userid=254
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127677

ARbitOUR;461600 Wrote:
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!!!!*


.....OK, so there was a 'close event' in the save macro that re-enabled
the cut/copy right-click menus....problem solved when I delete the
'thisworkbook.close' line. Unfortunately the end-users now have to
manually close the saved quotations (clearly manual closing does not
fire the macro IF the main template is already open).

LOL...


--
ARbitOUR
------------------------------------------------------------------------
ARbitOUR's Profile: http://www.thecodecage.com/forumz/member.php?userid=254
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127677