Free Microsoft Excel 2013 Quick Reference

Deleting personal xls macro Results

Can someone please help me. I am stuck with this frustrating macro. Every time I have a workbook open and I go to open another workbook, a warning pops up saying this is read only due to personal.xls.

I tried to create a personal.xls macro one time, and don't even remember what sheet it is in. I may have already deleted, I don't know. Is there anyway to clear all personal.xls macros so there is none.

Thanks for any help

Josh

I have a report I generate daily. From another application I see if Excel is running. If Excel is not I start Excel with the report file in the command line and the report is generated. If Excel is open I have the ability to call a macro from the other application (but not send a file open command). I am trying to use a macro in personal.xls to open my report file. It does open the file but then hangs at the “Workbooks(File_Name).Activate” line. Another thing I noticed is the VBA code up to that line did not happen. I am at a loss here so any help would be greatly appreciated. Here is the code I am using.

Personal.xls
Dim AlarmLoc As Variant
Dim AlarmFile As Variant
Dim AlarmOpenPath As Variant

Sub ExcelOpenAlarm()
AlarmLoc = "C:C7_LineAlarms"
AlarmFile = "Alarms.xls"
AlarmOpenPath = AlarmLoc + AlarmFile
Application.Workbooks.Open (AlarmOpenPath)
Workbooks("Alarms.xls").Activate
End
End Sub

Alarms.xls
Dim File_Dir As String
Dim File_Name As String
Dim File_Dir_Name As String
Dim newHour, newMinute, newSecond, waittime
Dim wbActiveBook As Workbook
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Private Sub Workbook_Activate()
'Auto runs report on file open rename file to debug
If ActiveWorkbook.Name = "Alarms.xls" Then
Call MakeAlarmLog
End If
End Sub

Sub MakeAlarmLog()
Application.ScreenUpdating = False
'Make date portion of file name
Range("I7").Select
Selection.Copy
Range("I8").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'Refresh DB query
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
'Retreive file name from spread sheet
File_Dir = "C:C7_LINEAlarms"
File_Name = ThisWorkbook.Sheets("Sheet1").Range("I8").Value
File_Dir_Name = File_Dir & File_Name
'Initial save file
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=File_Dir_Name, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'Clear non alarm DB junk from spreadsheet
Workbooks(File_Name).Activate
Columns("G:J").Select
Selection.Delete
Range("a1").Select
'Remove query reference
For Each Qy In Sheet1.QueryTables: Qy.Delete: Next Qy
'Delete Macros from Report
Set wbActiveBook = ActiveWorkbook
Set VBComps = wbActiveBook.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
'Restore default settings save file and close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
ActiveWorkbook.Save
If Workbooks.Count = 2 Then
Application.Quit
Else
ActiveWorkbook.Close
End If
End Sub

Also as another item I would like to make sure there are no files named Alarms.xls already open so all these functions can happen without any user intervention.

Best regards,
MN Mike the chilly VBA novice

As background, this macro (which I got from this forum) works just fine in my personal.xls macro list. However, I was trying to pass it along to someone else and at some point they had deleted their personal.xls file. So, I went through the process of

creating a workbook called personal.xls
dropping it in their XLSTART folder
hiding the workbook and saving it

Everything seems to be fine. Their EXCEL now opens with the typical default workbook and I can see the personal.xls in the VBA explorer.

However, this macro doesn't work for them if it is saved into the personal.xls workbook. It is supposed to take the active workbook and look at the items in a list and create a separate tab for each of the unique values. Stepping through it

it finds a unique value list (in this case from column one)
Creates a tab called UniqueList
Creates all the necessary tabs and copies the appropriate info
Deletes the UniqueList tab

Let's say that the workbook we want to modify is called TEST. The problem seems to be that it keeps making the personal.xls file the active workbook at some point. So, when the macro runs, it will find the unique values on TEST workbook, but create the UniqueList tab in the personal.xls workbook (with the unique values from the TEST workbook). And then error out.

Here is the code

Sub SplitIntoWorksheets()
'Declare variables
Dim rRange As Range, rCell As Range
Dim wSheet As Worksheet, wSheetStart As Worksheet
Dim strTitle As String, fCol As Long

'Speed up execution
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

'Set activesheet to a variable name so we can refer to it easily
    Set wSheetStart = ActiveSheet

'Turn off the Autofilter in case it got left on accidentally
    wSheetStart.AutoFilterMode = False

'Enter the column # here to evaluate, column A = 1
    fCol = 1
  
'Set a range out the values in the chosen column
    Set rRange = Range(Cells(1, fCol), Cells(Rows.Count, fCol).End(xlUp))
 
'Check if "UniqueList" sheet exists
    If Not Evaluate("ISREF(UniqueList!A1)") Then
        Worksheets.Add().Name = "UniqueList"        'add it if needed
    Else
        Worksheets("UniqueList").Cells.Clear        'clear it if it exists already
    End If
    
'Filter the Set rRange so unique item list is created
    With Worksheets("UniqueList")
        rRange.AdvancedFilter xlFilterCopy, , Worksheets("UniqueList").Range("A1"), True

'Set the rRange variable to the unique list of values, without the heading
        Set rRange = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With

'Starting with the original data sheet again...
    With wSheetStart
    '...Evaluate the rRange items against the data sheet one unique value at a time
        For Each rCell In rRange
        'create a version of the value with no spaces in it so it can be used as sheetname
            strTitle = Left(Replace(rCell, " ", "_"), 31)
        'Filter the original data by the field:=fCol and the criteria1:=rcell
            .Range("A1").AutoFilter fCol, rCell
        
        'Test to see if a sheet already exists for this value
            If Not Evaluate("ISREF('" & strTitle & "'!A1)") Then
                Worksheets.Add().Name = strTitle     'add it if needed
            Else
                Worksheets(strTitle).Cells.Clear     'clear it if it exists
            End If
    
        'Copy filtered data (visible data only) to the new/cleared sheet
            .UsedRange.Copy Destination:=Worksheets(strTitle).Range("A1")

        'Clean up the new sheet's appearance
            Worksheets(strTitle).Cells.Columns.AutoFit

        'Loop around to the next unique value
        Next rCell

    'When all values are processed, turn off the Autofilter in the data
        .AutoFilterMode = False

    'Return to the data sheet
        .Activate
    End With

'delete UniqueList worksheet
    Sheets("UniqueList").Select
    ActiveWindow.SelectedSheets.Delete

'reactivate application settings turned off earlier for speed
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
     
End Sub


By accident when I was creating a macro I saved it on the personal.xls how do I delete this?

Hi all!!!

I am trying to distribute a new macro to all users in my company. However, I don't want to delete those macros users have created in their Personal.xls.

I have tried to add a new workbook in xlStart with a different name. It works (that is, macros in this new workbook are available when someone opens Excel) but it has a problem: when Excel is opened, there is no workbook as default (all in blank). So the user has to click File -> New.

A possible workaround is including too a template in xlStart. However, is there any other solution?

Thanks.

Ramon.

Everytime I try to run a macro I get the following message "A document with the name Personal.xls is already open. You cannot open 2 documents with the same name even if they are in different folders. To open the second document, either close the document that's currently open or rename one of the documents."

I've searched my computer and only have one Personal.xls file and that's in the xlstart folder.

I've tried closing the file (thru window, unhide) but then get this message:- Personal.xls could not be found (then it asks me to check the file name and that it hasn't been deleted etc etc).

I tried to delete the file and save another(blank worksheet) in it's place under the same name but when I came to attach a new macro to an icon on my toolbar, up came another message telling me that an error had occurred and excel would now close.

I even tried to uninstall Office yesterday but when I reinstalled it - everything was the same, it's as if the registry remembered it all !!!!!

Can anyone help - this is driving me nuts

Thanks

Hello people,

Mabe somebody can help me with this:

I was testing the optional hidden macro book "personal.xlsm", I stored it in XLSTART folder,but
looks like I did something really wrong, because when I open Excel always loads without a new book
, instead of that, Excel loads the regular menues with an empty grey window. If I open another
Excel file, it loads normal.

I deleted Personal.xls and Personal xlsm from XLSTART folder but the behavior I said above continues
appearing when I launch Excel. In addition, I can see Personal.xls and Personal.xlsm in VBA editor
panel (Alt+F11) and don´t show any macro recorded.

How can I recover the normal Excel behavior when is launched?
How can I delete deleted Personal.xls and Personal xlsm from VBA Editor panel?

I´ll appreciate any advice.

Best regards

I have a Macro for highlighting current cell:

Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
ActiveCell.FormatConditions.Add xlExpression, , True
ActiveCell.FormatConditions(1).Interior.Color = vbYellow
End Sub

Whenever I open Personal.xls it works fine, meaning, current cell is highlighted in Yellow but when I open any other Book/File it does not highlights current cell in Yellow.

What should I do to make this Macro available on open for all worksheets?I am using Excel 2003

Thank you.

While working in Visual Basic, there was an excel error which resulted in of my PERSONAL.xls file to be automatically copied. The copy is named PERSONAL (version1).xls.

This copy contains all the same macros as the original although the the changes to the macro I was working on only appear in the copy. I have copied those macro changes to the macro in the original file and now want to delete the copy. Where do I find it? Is anyone aware if there would be anything else I need to consider before deleting?

Thank you.

Paula

I have a basic macro created in my personal workbook which is used to run the standard pop up calendar (calendar addin) on any workbook.

I have also set up other users with the same 'pop up calendar'. These other user never had a 'personal' woorkbook, so to create one I simply recorded a blank macro (in personal workbook).

What I have noticed is that my personal workbook is called:
Personal.XSLB (notice a 'B' on the end)

While others sre called:
Personal.XLS (no 'B' on the end)

This is a problem as the calendar popup will not run if the macro that runs it is looking for Persoanl.XLSB when their workbook is called Personal.XLS

My workbook is the od one out, what can I do, I cant rename the work book, can I delete the workbook and create a new one ?

Please help
Thanking you in advance.
Springbrook

Having Personal.xls *is* handy. But when starting a "new" workbook,
Excel *assumes* I mean "Personal.xls" and opens "it". If I save it as
my new book, it contains my macros from "Personal.xls". And I can't
seem to DELETE all the macros from within the workbook that was
originally "Personal.xls".

I've moved (saved) Personal.xls and deleted it from XLSTART, so Excel
won't open it, while I try to fix my "new" book. I *still* can't.

What's the secret to starting a new workbook? Devoid of macros, or
whatever? Having one lying around called "BLANK.XLS" so Excel won't
assume I mean Personal.xls?

This is driving me crazy. Anyone have any ideas, thoughts,
suggestions?

Thanks in advance,

Tom

Hi There,

I'm using Office XP and I'm having a problem with the macros
registered in my personal.xls file. When I launch Excel manually, the
macros are accessible and function correctly. The macros do no work
however when Excel is launched by a specific third-party program that
I have. I have tried a bunch of things to get the macros working but
I'm stumped. If anyone can offer any suggestions, it would be greatly
appreciated. I've tried the following so far:

1. Deleting the personal.xls file and recreating it through Excel.
Excel created the file in "C:Documents and Settings[USER_NAME]
Application DataMicrosoftExcelXLSTART" rather than "C:Program
FilesMicrosoft OfficeOffice10XLStart". I found this a little odd.

2. Copying the personal.xls created in the "Documents and Settings"
folder (see 1) into the XLStart folder in the "Program Files" folder.

2. A repair of Office

3. A reinstall of Office

Thanks!
Kris

Hello,
I am somewhat new when it comes to macros. I have recorded a few but I
am in new territory right now. After trial and error I finally figured
out how to get my existing personal.xls that is on my work computer
running Excel 2003 onto a new computer running Excel 2007.(XP for both
OS). I copied my existing personal.xls into C:/Program Files/Microsoft/
Office12/XLStart (if it matters) and it seems to be working fine.
However, during my trial and error, I ended up adding a Personal.xlsb
file. Currently this file has nothing in it. What is the difference
between .xls and .xlsb? If I do not need it, I would like to delete
it- and I am not sure how.
Another thing that is different is now when I open Excel, the blank
workbook (book1) does not appear. It is just a blank blue screen with
the Excel toolbar on top. I have to do a couple of steps to create and
open a blank workbook. Also, when try to close down Excel, I hit the
very top right "X" and only the workbook disappears. I am back at the
blank blue screen with the Excel toolbar on top. I was hoping to get
this back to standard version, ie when I open Excel, a new workbook
shows up and when I close it down, Excel itself will close.

As I said before, I am somewhat new with macros and any advice and
suggestions would be greatly appreciated. Thank you for your time in
advance.

Hello all!

A quick question.
I was trying to delete some macros that I had recorded and ar
available on the personal.xls.
But am getting a message that says quote "cannot edit a macro on th
hideen workbook. Unhide this workbook using the unhide command". Thi
happens only when I try to delete the macros that are available i
personal.xls. I could always delete the other macros that are availabl
in all workbooks, the ones which were recodred and were not mad
available to personal.xls. How do I get rid of this thing?
The workbook is not hided at all.

One more question. If we declare some public/global variables and us
the same in multiple macros, those macros should be made available i
personal.xls, while being recorded...is'nt it? Am I missing anythin
here?

I would appreciate your help guys.
Thanks a bunch.

Arun...
Vtec Corp

--
Message posted from http://www.ExcelForum.com

I accidentally created a macro module in my VBAProject (PERSONAL.XLS) then
deleted it. Now the (PERSONAL.XLS) is always visible in the projects tab. How
do I hide the (PERSONAL.XLS) again?

Okay, I feel really stupid, but I can't figure this one out.

As I come across nifty macros that I want to use in multiple spreadsheets I record them in my personal.xls. For some I've set up a macro shortcut. Today I went to use a macro to unhide all hidden worksheets, and couldn't remember the shortcut. I'm able to run the macro manually, so all is not lost, and I could delete and recreate the macro with a new shortcut, so I can certainly get around this problem. However, if it happens once I'm sure it will happen again.

So, my question is, how do I view the shortcut key I've assigned to the macro? Everywhere I look online it says to open the Macro dialog box, select the macro in question, and go to the Options button. Well, for some strange reason I don't have that Options button. How do I get it back?

Also, is there a nifty macro that would generate a list of my macros with the shortcut keys assigned?

Thanks in advance for any help you can provide on this.

I've got another personal.xls i want to put in its place. So one would think you could find it here C:Program FilesMicrosoft OfficeOFFICE11XLSTART, and delete it. That's exactly what i did. I deleted it and put the new one in it's place. I opened excel and noticed something weird...it was still using the old one, even though it's no longer there. I log off, never start excel, use Google desktop search to try to find it. Nope, just my version of it. So does anyone know what I'm doing wrong? At this point, I've renamed my version Macro.xls so i don't get them confused.

Thanks,

Chris

Hi All,
I would like to ask a question. Looking to to the Modules in
my PERSONAL.xls I see a lot of them. Something about 60. Looking into these
Modules I see almost all of them are empty. I suppose they were created on
the base of macro recording etc. Is it possible to remove all empty Modules?
I
suppose there should be the same number of Modules as I have the number of
macros in my PERSONAL.xls (listed in the list of macros). If possible to
delete them will they be automatically renumerated? I mean from Module1 to
Module15 for example. Is it possible to set up the Excel/VBA to delete
automatically an empty module?

Would like to delete a macro form personal.xls.
It shows in VBE, but when I try to delete, it tells me I must unhide it.

I changed my folder options to show hidden files, and did a search but cannot find it. I have no files in my OfficeXLStart folders either.

Advice ??

Thanks
Danna

Since I am working on creating templates for a company that is going to be needing to edit these templates in the future, I'd like to have a way to not include the macros in the actual template itself but instead create a separate document with all the macros for all the templates in it (sort of my like own Personal.xls would be) but include an extra macro that will automatically move all of these macros to the users own Personal.xls when they open it (or maybe click a button).

I don't know what would be involved in order to create this, especially considering the fact that some users may have deleted their Personal.xls in the past or many other things could have happened.

I would think the best way to do this would be to somehow have the macro create a new workbook, name it something like "Clothing_Template_Macros.xls" and save it it to their startup folder with the macros in it...can this be done without knowing the exact location of their startup folder?

And on top of all that, I was hoping for it to have an attached toolbar with buttons linking to the different macros...but I would assume it has to know where this workbook is going to be located for it to link correctly.

Anybody ever tried to do something like this before?

Is this all just a pipe dream?


No luck finding an answer? You could always try Google.