''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''Anothe User Can Play The Game
'''''suppose u send the another user a workbook
''''and it has a security with vba restriction
''''he can disable macro using
''''Tools ,Macro ,Security,
'''''Security level Very High
''''and trusted publisher none(uncheck both check box)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''what to do
now'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''Answer is force user to enable macro
''''Try this code in workbook(This Workbook)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
Const WelcomePage = "Macros"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False
'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to
'" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Call customized save routine
Call CustomSave
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If
'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False
'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True
'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub
Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
End Sub
Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False
'Record active worksheet
Set aWs = ActiveSheet
'Hide all sheets
Call HideAllSheets
'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If
'Restore file to where user was
Call ShowAllSheets
aWs.Activate
'Restore screen updates
Application.ScreenUpdating = True
End Sub
Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet
Worksheets(WelcomePage).Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws
Worksheets(WelcomePage).Activate
End Sub
Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'In my example i have sheet1,sheet2,sheet3,sheet4
'i have used this in excel 2003 and excel 2007
Hope It Will Help
Happy To Help
:)
Shyam
On Wed, Mar 10, 2010 at 4:13 PM, rf1234 rf1234 <rfhyd1...@gmail.com> wrote:
> Hello Dear
> Suppose we have write a macro for book1 using excel 2003,
> when we send it to another user who have excel 2007 or
> if he Disable or not allowing the macro using
>
> Tools->Macro->Security
>
> then the problem occurs,best way is forcely run macro at
> startup or openning mode of workbook.
>
>
>
> Hope It Will Work
> Happy To Help
> :)
> Shyam
>
>
>
>
> On Wed, Mar 10, 2010 at 3:59 AM, Excel 009 <excelmodel...@gmail.com>wrote:
>
>> Hi Shyam,
>>
>> Thank you for your help. I used similar code to disable the copy/cut/
>> paste on the key combination and on the toolbar and right click. It
>> works well. However, when one has the clipboard pane opened, one can
>> still copy and paste using the command in the pane. When I used 2003,
>> I can pretty much disable all the copy/cut/paste functionality.
>> However, one can always open the 2003 file in Excel 2007, open the
>> clipboard pane, copy or cut using the command on the ribbon, and paste
>> the copied object by clicking on the object inside the pane. As it is
>> not easy to modify the ribbon (through changing in XLM), I did not
>> look into that option.
>>
>> The easiest way to avoid user from copy the current content to other
>> workbooks, if possible, is to empty the clipboard as it will ease all
>> the possible ways (ex: insert the copy/paste command from toolbar
>> option into the workbook) that people use the copy/paste functions.
>> So again, we are back to our starting point - how to empty the
>> clipboard programmatically.
>>
>> Kavita showed a method to do this, but it does not work as it cannot
>> stop the copied object to appear on the clipboard.
>>
>> For AJ, this method applies the effect at the Excel "application
>> level" and not on Excel "workbook level". How can we set it up so
>> that the users will have this option setting on their end without
>> asking them to do this on their own? Also, how can we stop them from
>> turn the functionality back on?
>>
>>
>> Excel 009
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our Facebook Group @
>> http://www.facebook.com/group.php?gid=287779555678
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> HELP US GROW !!
>>
>> We reach over 6,800 subscribers worldwide and receive many nice notes
>> about the learning and support from the group.Let friends and co-workers
>> know they can subscribe to group at
>> http://groups.google.com/group/excel-macros/subscribe
>>
>
>
>
> --
>
> Thanks Regards
> Shyam
> Software Engineer
>
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> When one door of happiness closes, another opens;
> but often we look so long at the closed door that we do not see the
> one which has been opened for us.
> - Helen Keller
>
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> Wenn eine Tür des Glücks schließt, öffnet sich ein weiteres,
> aber oft schauen wir so lange auf die geschlossene Tür,
> dass wir nicht sehen ein, die für uns geöffnet wurde. - Helen Keller
>
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>
--
Thanks Regards
Shyam
Software Engineer
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
When one door of happiness closes, another opens;
but often we look so long at the closed door that we do not see the
one which has been opened for us.
- Helen Keller
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Wenn eine Tür des Glücks schließt, öffnet sich ein weiteres,
aber oft schauen wir so lange auf die geschlossene Tür,
dass wir nicht sehen ein, die für uns geöffnet wurde. - Helen Keller
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
