Posts by EdFromAZ

Re: XL2007:Setting ExclusiveAccess stops coded dead

Well, to add more fuel for thought, I also tried these methods:

-- I pulled out the code to set ExclusiveAccess and wrote in it into a VBScript, executed the script, and looped the macro until MultiUserEditing was false

-- I used SaveAs with Access as xlExclusive

-- I set the workbook object to Nothing, used Activeworkbook exactly like the Help article says, and reset the workbook object
In every case, the method to turn off sharing worked. But the code immediately stopped dead.

About the only thing I haven't tried (because it's not available to me) is to put all this in a Visual Basic exe and run it totally independant of the Excel VBE. If no one else has any answers, that may be where I need to go with this.

Ed

Re: XL2007:Setting ExclusiveAccess stops coded dead

I did some more investigating. I know the Excel help has a procedure using the ActiveWorkbook to enable ExclusiveAccess, and I've seen it recommended in more than one on-line posting. So I set up a separate macro with the Help code, bracketed by DisplayAlerts, and it worked great!! Stuck a MsgBox at the end just to make sure code processing was continued and it was.

So then I called that procedure from within my other code - and it broke it dead! I thought maybe it needed a different object (I dunno - I'm throwing everything AND the kitchen sink at this!), so I set that up too.

As before, the file opens, sharing does get turned off, but code processing stops dead at the ExclusiveAccess line.

Here's my revised test code:

Sub OpenAndUnshare()
Dim wkb As Workbook
Dim strWkbName As String
Dim secAutomation As MsoAutomationSecurity
Application.EnableCancelKey = xlInterrupt
On Error GoTo 0
'Get macro security level
secAutomation = Application.AutomationSecurity
'Get file name
strWkbName = Application.GetOpenFilename
'Open file
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Set wkb = Workbooks.Open(Filename:=strWkbName, UpdateLinks:=2)
      Debug.Print "Check_1"
Application.AutomationSecurity = secAutomation
      Debug.Print "Check_2"
Debug.Print ActiveWorkbook.Name
      Debug.Print "Check_3"
'Turn off sharing
Call UnshareMe
Stop
End Sub
 
 
Sub UnshareMe()
Debug.Print ActiveWorkbook.Name
      Debug.Print "Check_4"
Dim wkb2 As Workbook
Set wkb2 = ActiveWorkbook
Application.DisplayAlerts = False
If wkb2.MultiUserEditing Then _
  wkb2.ExclusiveAccess
Application.DisplayAlerts = True
      Debug.Print "Check_5"
MsgBox "Sharing off"
End Sub

With XL2007 and Vista, I am opening workbooks and modifying them. Some of these are shared, and all have macros. I am trying to set ExclusingAccess to remove sharing, but as soon as I do that, the code stops dead. Sharing is removed, but nothing after that line is processed.

Below is the macro I'm using. I set the Debug.Print statements to see where it's breaking - I get "Check_6", which is the line before the ExclusiveAccess statement, but I never get "Check_7", even though sharing _is_ removed from the file. As you can see, I've tried just about every setting I can to avoid this, but to no avail.

If anyone has the magic bullet, I will be so glad!!
Ed

Sub OpenAndUnshare()
Dim wkb As Workbook
Dim strWkbName As String
Dim secAutomation As MsoAutomationSecurity
Application.EnableCancelKey = xlInterrupt
On Error GoTo 0
'Get macro security level
secAutomation = Application.AutomationSecurity
'Get file name
strWkbName = Application.GetOpenFilename
'Open file
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Set wkb = Workbooks.Open(Filename:=strWkbName, UpdateLinks:=2)
      Debug.Print "Check_1"
Application.AutomationSecurity = secAutomation
      Debug.Print "Check_2"
'Turn off sharing
If wkb.MultiUserEditing = True Then
      Debug.Print "Check_3"
  Application.DisplayAlerts = False
  DoEvents
      Debug.Print "Check_4"
  'Application.EnableCancelKey = xlDisabled
      Debug.Print "Check_5"
  On Error Resume Next
      Debug.Print "Check_6"
  wkb.ExclusiveAccess
  DoEvents
  On Error GoTo 0
      Debug.Print "Check_7"
  'Application.EnableCancelKey = xlInterrupt
      Debug.Print "Check_8"
  Application.DisplayAlerts = True
      Debug.Print "Check_9"
End If
      Debug.Print "Check_10"
Stop
End Sub

Re: XL2007: Macro breaks when copying sheet from one workbook into another

Okay - I think I'm on to something!
While the ~TAB~ name of the sheets are different,
the "CODENAME" (Sheet1, Sheet2, etc) is the same!!
And as soon as that hits the receiving workbook, we're done.

I can't access the VBE to change the CODENAME.
So ... create a new worksheet, copy the original, and paste eveything?
And cross the fingers?? That's a lot of conditional formatting, formulas -- and data!!

Anyone have experience with this that might help?
Ed

I think I've finally got the rest of my errors and breaks handled, but I just can't figure this one out. I'm giving my users a workbook containing updated code and forms; this will be used to update the workbooks they are already using. Corporate security prevents me from accessing and writing to the VB Project, so the best I can do is give them this workbook which will pull in (copy) their data sheets and fix some formulas, leaving them with the same data and worksheets on top of new and improved code and forms.

Here's how it works:

-- User clicks a cell, which triggers a Selection_Change event, which calls my main macro
-- The main workbook copies itself; this copy will be the “base” of the improvements
-- OPEN dialog is used to gather names of files to be fixed; these are written into the main workbook
-- Iterate through list of files and open, copy worksheets, fix stuff, save, and close

It all works, for the most part. But if it’s gonna break, it will be in the action to copy the sheets over. If I F8 or F5, it runs fine. But If I start from clicking the cell like my users will, there’s a good chance it will break.

The break always happens after copying the first sheet. The sheet is copied - and the code simply stops. Even when I wasn’t trying to suppress alerts and such, it would just stop dead. I’ve got a log file running to record events, and the log file statement never gets run. But the sheet is copied.

The sheets being copied do contain formulas and named ranged. Before I added Application.EnableCancelKey = xlDisabled, I'd always get alerts asking me if I wanted to keep or replace the names.

Any hints on overcoming this are greatly appreciated!
Ed

[FONT=Times New Roman][size=12]‘Yes, all objests are declared and point to the correct item[/SIZE][/FONT]
[FONT=Times New Roman][size=12]Sub CopySheetsOver()[/SIZE][/FONT]
 
[FONT=Times New Roman][size=12]'Copy every sheet[/SIZE][/FONT]
[FONT=Times New Roman][size=12]For Each wks1 In wkbTracker.Worksheets[/SIZE][/FONT]
[size=12][FONT=Times New Roman]  If wks1.Name <> "FAQs" Then[/FONT][/SIZE]
[size=12][FONT=Times New Roman]    Call RmvProt[/FONT][/SIZE]
 
[size=12][FONT=Times New Roman]    a = wkbNew.Worksheets.Count[/FONT][/SIZE]
[size=12][FONT=Times New Roman]    DoEvents[/FONT][/SIZE]
[size=12][FONT=Times New Roman]    Application.DisplayAlerts = False[/FONT][/SIZE]
[size=12][FONT=Times New Roman]    Application.EnableCancelKey = xlDisabled[/FONT][/SIZE]
[size=12][FONT=Times New Roman]    wks1.Copy After:=wkbNew.Sheets(a)[/FONT][/SIZE]
[size=12][FONT=Times New Roman]    DoEvents[/FONT][/SIZE]
 '''~~The next line is not run~~
[FONT=Times New Roman][size=12]'**********************[/SIZE][/FONT]
[FONT=Times New Roman][size=12]LogInformation ("Copied over " & wks1.Name)[/SIZE][/FONT]
[FONT=Times New Roman][size=12]'**********************[/SIZE][/FONT]
 
[size=12][FONT=Times New Roman]    Application.EnableCancelKey = xlInterrupt[/FONT][/SIZE]
[size=12][FONT=Times New Roman]    Application.DisplayAlerts = True[/FONT][/SIZE]
[size=12][FONT=Times New Roman]  End If[/FONT][/SIZE]
[FONT=Times New Roman][size=12]Next wks1[/SIZE][/FONT]

Re: Macro refuses to finish running against a file on the server

Yah - I can do it there.
*sigh*
One more weekend project!!

Thanks for the boost.
Ed

Re: Macro refuses to finish running against a file on the server

Looks sweet - in fact, I'd swear I did use it long time ago! Unfortunately, my current corporate environment will not allow me to download and install any non-approved exe. If it were an Add-In, I could probably do it.

But his notes say it's about exporting all modules to text files, deleting the modules, and then importing the text files as modules. I might try that manually. I gotta do something - I've got some buggy files out there and need to make some changes. (I imagine that if I were good enough to not have buggy files, I wouldn't be having this problem either!)

Thanks for responding!
Ed

Okay - I've finished my magnum opus. This is meant to make a number of different changes to workbooks in use. I've tested and tweaked it until it ran fine against my test file on my own machine.

But since most of the files it will run against are located on a server (within the corporate intranet on this local compound) somewhere, I obtained a "real-world" test file and put it into a server folder and ran my macro program. And it fails. It opens the workbook and begins the actions, but stops dead. I'm wondering if perhaps it's got something to do with a time lag working on a file over the network.

Here's what I do know --

-- Basic actions to the point of failure:
>> Makes a copy of the Updater workbook
>> Opens the file to be updated
>> Makes a copy of the file to be updated
>> Creates a new workbook using the copy of the Updater as a template
>> Copies over the worksheets of the networked file into the new workbook.
... and it fails in the middle of copying the sheets.

-- If I begin the macro in the VBE window and hold down the F8 key, it runs through every single step from beginning to end with no errors, no stoppages, no problems.

-- I set my options to Break on Every Error, and in the Sub where the worksheets are copied over from the original file into the new file it stops on the second line of this:

wks1.Copy After:=wkbNew.Sheets(wkbNew.Worksheets.Count)
  Set wks2 = wkbNew.Sheets(wkbNew.Worksheets.Count)

When the alert dialog pops up, I can hit Continue and it does without a problem.
(side note: it also seems to think every boolean and most Ifs are errors, too!!)

-- I found several posts on the internet about random stoppages being resolved by using
Application.EnableCancelKey = xlDisabled
so I threw that in very liberally. I went back and commented all of them to see if I might get an error when this happens. No go - the code simply stops executing and dies when I click my button and let it run. When I step through it manually, it stops with the Code execution has been interrupted dialog at just about every step; Continue gets me going again for another one to three steps.

I'm obviously not the best at doing all of this, but it sure seems like the problem is caused by the prgram running faster than the actual file, and the code is finished before the file is fully caught up to what just happened.

So other than throwing a 5-second Sleep in between each line to slow things down, is there anything I can do?

Ed

Re: XL2007: Error when entering formula using VBA

Of course - it _would_ jump out at me *after* I posted!!

When I changed
INDIRECT("R"&ROW()&"C"&COLUMN(StartRow)
I initially came up with
INDIRECT(" & Chr(34) & "R" & Chr(34) & "ROW()&" & Chr(34) & "C" & Chr(34) & "COLUMN(StartRow)
This neglected the & between R and ROW and between C and COLUMN.
Fixed, it looks like this:
INDIRECT(" & Chr(34) & "R" & Chr(34) & "&ROW()&" & Chr(34) & "C" & Chr(34) & "&COLUMN(StartRow)
and enters with no errors.

Ed

I'm trying to enter formulas in cells using a macro in XL2007. It's working - until I come to one formula. Then I get "Application-defined or object-defined error".

I built the formula inside the worksheet, so I know it's acceptable and working as a worksheet formula. I think my problem came when I tried to add in Chr(34) as required to account for text values entered in the formula.

I've 'bout gone crazy trying to find my problem - if anyone can lend a fresh set of eyes, I would be most grateful.

The good formula in the worksheet:

=IF($H6<>"",IF(ISERROR(SEARCH($I6,RIGHT(DCPrefix,LEN(DCPrefix)-SEARCH(":",DCPrefix,1)),1)),"",IF(INDIRECT("R"&ROW()&"C"&COLUMN(StartRow)+1,FALSE)<>"",IF($H6="",IF(TODAY()>INDIRECT("R"&ROW()&"C"&COLUMN(StartRow)+1,FALSE),"Late","No Data"),IF($H6>INDIRECT("R"&ROW()&"C"&COLUMN(StartRow)+1,FALSE),"Late","Good")),"No Data")),"")

The formula in VBA that chokes:

"=IF($H6<>" & Chr(34) & Chr(34) & ",IF(ISERROR(SEARCH($I6,RIGHT(DCPrefix,LEN(DCPrefix)-SEARCH(" & Chr(34) & ":" & Chr(34) & ",DCPrefix,1)),1))," & Chr(34) & Chr(34) & ",IF(INDIRECT(" & Chr(34) & "R" & Chr(34) & "ROW()&" & Chr(34) & "C" & Chr(34) & "COLUMN(StartRow)+1,FALSE)<>" & Chr(34) & Chr(34) & ",IF($H6=" & Chr(34) & Chr(34) & ",IF(TODAY()>INDIRECT(" & Chr(34) & "R" & Chr(34) & "ROW()&" & Chr(34) & "C" & Chr(34) & "COLUMN(StartRow)+1,FALSE)," & Chr(34) & "Late" & Chr(34) & "," & Chr(34) & "No Date" & Chr(34) & "),IF($H6>INDIRECT(" & Chr(34) & "R" & Chr(34) & "ROW()&" & Chr(34) & "C" & Chr(34) & "COLUMN(StartRow)+1,FALSE)," & Chr(34) & "Late" & Chr(34) & "," & Chr(34) & "Good" & Chr(34) & "))," & Chr(34) & "No Date" & Chr(34) & "))," & Chr(34) & Chr(34) & ")"

(Note: DCPrefix and StartRow are named ranges in the worksheet.)

Ed

Re: XL2007: Is there a worksheet protection option that disables View &gt;&gt; Split?

Nope - can't use either of those options. They lock out too much.
Thanks for the info, though.

Ed

Re: XL2007: Is there a worksheet protection option that disables View &gt;&gt; Split?

Thank you!! The Windows option seems to work for me ... but we'll see if anyone screams after I use it in my next Update!!

Ed

I have a "master workbook" set up with locked cells, Freeze Panes on F6, and woksheet-level protection with a password. Others copy this workbook, set it up for their own projects, and Share those workbooks among their team.

With the worksheet protected, the Freeze Panes button is disabled. But it seems a team member has found the Split button. Turning Split on and then off removes the freeze, but doesn't remove either the sharing or the protection. So to reset the Freeze Pane, I have to physically go to the location, unshare, unlock, Freeze Panes, re-lock, and share.

Much better if I could somehow disable the Split button, I think.
Any suggestions on making this happen?

Ed

Re: What's the difference between ProtectSharing and Protect with SaveAs/xlShared?

Because I had no response here, I also asked this question in the Technet Excel forum (http://tinyurl.com/3bf2exl).

Ed

In XL2007 VBA, what is the difference in the user interaction and protection afforded between:
-- using worksheet.protect with a password, then SaveAs with xlShared?
or
-- using workbook.ProtectSharing with a password?

Right now, we have files that are shared with certain sheets password-protected. I have users complaining about not being able to enter comments in cells, not being able to merge cells, and a few other things. But I don't want them messing with certain cells (overwriting values and formulas, unhiding columns to view info, etc.).

Where can I understand the difference between the levels of protection and amount of user interaction afforded by these two options?

Ed

Re: Recover Add-in: &quot;project unviewable&quot; - but I was just working in it!!

Okay - found the issue! A misstep in my code had accidently turned on Sharing in my Add-in. It's all recovered now.

Ed

Vista Enterprise; XL2007. I had an add-in loaded and was tweaking the code. It locked up, so I closed it all out and restarted Excel. The add-in still loads, but now the VBA project is unviewable and won't open up! I took it out of the Add-Ins folder and put in another folder - it still won't open.

How can I recover my code work??
Ed

Re: Update VBA in file with &quot;Trust VBA Access&quot; prevented?

Reafidy:

Quote

[INDENT]Admitedly I dont know your company structure and I.T. system. But on the face of it I would say thats a potential for a much bigger headache than an addin. [/INDENT]

What are you looking at for the headache? The IT environment or the way I'm proposing to handle things?

rory:
I did not know an add-in can be run from a network location!! At the moment, I have the "Master" file as Read-Only in a network folder; the users download to their computer and then SaveAs their own file. Can I safely assume that if I had the add-in linked to the master file, it would stay connected throughout the download/drag-and-copy/right-click & SaveAs process and then the user's modifications and saving?

Then again, some of the code in this is in Worksheet_Change procedures. Can I duplicate those in an add-in?? The users will be adding some new sheets, and then also making copies of a specific sheet to enter more information on.

Ed

Re: Update VBA in file with &quot;Trust VBA Access&quot; prevented?

I'm very hesitant about using an add-in. Trying to guide it into the correct folder can be a nightmare! It would indeed be great, though. No, I don't have access to all their folders - I'm not an admin type, not really even a developer - just the guy who knew tricky stuff with Excel when we needed a new log file built!

So I think I'll look at what it's going to take to copy over each one of their worksheets into a new file. Probably go something like:
-- Distribute a file with all the corrected code and one sheet with a button on it: "Click me to update"
-- Code directs them to navigate to file to be updated
-- Copy each sheet from old file into "update" file (hopefully there's a "sheet copy" method that doesn't lose formatting or formulas)
-- Do any necessary formula updates in the new book
-- Rename original file a "_old" and save
-- SaveAs update file with the original file's fullname
Hmm - is there a property of the SaveAs that creates a new file but leaves the original? If not, I'll have it clone itself first - somehow. Some of these poeple are going to have to run this near a dozen times!

Thanks for chiming in.
Ed

Re: XL2007: Allow UNDO of macro steps?

cytop:
I do ask for confirmation, and I may put in one more level. Someone suggested I highlight the rows that will be deleted and ask "Are you really sure this is what you want to delete?" And really, after that point, it's just not my fault!! 8>)

rory:
Thank you for that link! I've got his Excel 2000 Bible, and his stuff is great. I'll look at it closer tomorrow.

Thanks to both of you for your help!
Ed