Autorun macro does not autorun...

A

ARbitOUR

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

....sigh....

Any help out there? aha!
 
S

Simon Lloyd

The line that you stated is re-opening the workbook ISN'T!, th
workbook, if it meets criteria is ACTIVATED well before that, the fac
that you
Code
-------------------
Set DatabaseWB = Workbooks.Open(.....
-------------------
doesn't mean it that it is opening it, it i
simply setting a variable to be used instead of typing the whole thin
it would be used like
Code
-------------------

Set DatabaseWB = Workbooks.Open(ThisWorkbook.Path & "\HC Price Lists.xlsm"
If Range("A1").Value = "Y" The
DatabaseW
End I

-------------------

If the workbook is indeed closed then you must trigger the reopen
[COLOR=#0000dd][/COLOR said:
=ARbitOUR;461600]Hi there

I have a macro in my 'Thisworkbook' object class that disables Cut/Cop
and 'drag-move', enabling it again after the workbook is closed. When
manually open the workbook the macro automatically functions as i
should (i.e: graying out / disabling the Cut/Copy right-click men
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 i
saved one can re-open the original Workbook template with a 're-open
macro. after executing the 're-open' macro, the original workbook (no
under a different file name) auto-saves itself, opens the origina
workbook template again, then closes

*For some reason, after executing the 're-open' macro, the 'disable cu
/ copy' macro in the 'Thisworkbook' object class does not do what it i
supposed to do: The Cut / Copy options in the right-click menu i
available again!!!!

If I close this workbook template and manualy re-open it again, th
'Disable_Cut_Copy' macro again functions as it should

I'm not sure if this problem is related to the 'Cut/Copy' macro or t
the 're-open' macro...

However....This template workbook contains a link to open another exce
workbook that also contains the exact same 'disable cut/copy' macro i
its 'Thisworkbook' object class...yet when it is opened via it'
specific macro it functions as it should, unlike when using th
're-open' macro to open the Template workbook....

Here's the code I use in the 'Thisworkbook' object class for disablin
Cut/Copy etc. upon opening of the workbook

Option Explici

Sub EnableControl(Id As Integer, Enabled As Boolean
Dim CB As CommandBa
Dim C As CommandBarContro
For Each CB In Application.CommandBar
Set C = CB.FindControl(Id:=Id, recursive:=True
If Not C Is Nothing Then C.Enabled = Enable
Nex
End Su

Private Sub Workbook_Activate(
EnableControl 21, False ' cu
EnableControl 19, False ' cop
EnableControl 22, False ' past
EnableControl 755, False ' pastespecia
Application.OnKey "^c", "
Application.OnKey "^v", "
Application.OnKey "+{DEL}", "
Application.OnKey "+{INSERT}", "
Application.CellDragAndDrop = Fals
End Su

Private Sub Workbook_BeforeClose(Cancel As Boolean
EnableControl 21, True ' cu
EnableControl 19, True ' cop
EnableControl 22, True ' past
EnableControl 755, True ' pastespecia
Application.OnKey "^c
Application.OnKey "^v
Application.OnKey "+{DEL}
Application.OnKey "+{INSERT}
Application.CellDragAndDrop = Tru
End Su

Private Sub Workbook_Deactivate(
EnableControl 21, True ' cu
EnableControl 19, True ' cop
EnableControl 22, True ' past
EnableControl 755, True ' pastespecia
Application.OnKey "^c
Application.OnKey "^v
Application.OnKey "+{DEL}
Application.OnKey "+{INSERT}
Application.CellDragAndDrop = Tru
End Su

Private Sub Workbook_Open(
EnableControl 21, False ' cu
EnableControl 19, False ' cop
EnableControl 22, False ' past
EnableControl 755, False ' pastespecia
Application.OnKey "^c", "
Application.OnKey "^v", "
Application.OnKey "+{DEL}", "
Application.OnKey "+{INSERT}", "
Application.CellDragAndDrop = Fals

End Su


And here's a sample of the code used to 're-open' the main templat
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 :mad:

....sigh....

Any help out there? aha!
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
 
A

ARbitOUR

Simon said:
The line that you stated is re-opening the workbook ISN'T!, th
workbook, if it meets criteria is ACTIVATED well before that, the fac
that you > Code
-------------------
simply setting a variable to be used instead of typing the whole thin
it would be used like > Code
-------------------
Set DatabaseWB = Workbooks.Open(ThisWorkbook.Path & "\HC Price Lists.xlsm")
If Range("A1").Value = "Y" Then
DatabaseWB
End I -------------------

If the workbook is indeed closed then you must trigger the reopen!
Click to expand...

Hi Simon,

First of, thx for the response.

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

The following code checks to see if it is already open...if it is ope
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 sampl
code is already open, if it is, then it is activated, if it isn't the
it is opened. There are NO other code in the macro to open the indicate
workbook only the line that you say does not open the workbook.....a
said yourself, it is a conditional opening of the workbook...(why ope
it if it is already open right?)

either way, in hindsight I see that the sample code I posted wa
actually the code where the 'cut/copy' macro works as it should (th
sample code is linked to a button control, when pressed, it opens th
price list database 'HC Price Lists' as it should (i.e with cut/cop
disabled).....So I accidentally posted the code that doesn't have th
problem. The code with the problem (i.e where the 'cut/copy' macr
doesn't function when 're-opening' via a macro - yet functionin
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! (onl
difference is the workbook names aren't the same)

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

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

Oh..the torture and misery!

PLEASE HELP
 
S

Simon Lloyd

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

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

End SubBe:private Sub SAMPLECODE_Click(
Click to expand...

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

End Su

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
 
A

ARbitOUR

Simon said:
Shouldn't this:Be:private Sub SAMPLECODE_Click()

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

End Sub
Click to expand...

You're right Simon...Type-O when I posted...it reads with a backslas
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 t
allow excel sufficient time to execute all the instructions in th
'disable cut/copy' macro...didn't help ('disable Copy/paste' stil
didn't execute)... However, I noticed that when the file has bee
re-opened (after it was saved) the 'disable cut/copy' macro doesn'
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 ha
executed....weird that...I'l re-check all the code in the save macro...

sig
 
A

ARbitOUR

ARbitOUR;461600 said:
Hi there!

I have a macro in my 'Thisworkbook' object class that disables Cut/Cop
and 'drag-move', enabling it again after the workbook is closed. When
manually open the workbook the macro automatically functions as i
should (i.e: graying out / disabling the Cut/Copy right-click men
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 i
saved one can re-open the original Workbook template with a 're-open
macro. after executing the 're-open' macro, the original workbook (no
under a different file name) auto-saves itself, opens the origina
workbook template again, then closes.

*For some reason, after executing the 're-open' macro, the 'disable cu
/ copy' macro in the 'Thisworkbook' object class does not do what it i
supposed to do: The Cut / Copy options in the right-click menu i
available again!!!!*
Click to expand...


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

LOL..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can't manually open file after coding. 0
Disable Edit > Fill 3
Change a great number of words in word files, with a macro as short as possible. 5
copy & paste from other worksheet - but not allow to cut 0
Enable copying function 5
Word macro that can call cells out of Excel for a find and replace 0
Making a macro only work on certain files 2
Copy and Paste Between Workbooks 2

Top