Macro Assignment Lost When File Name Is Changed

Nov 17, 2007

When I use customize and assign a macro (to a button) I set the "Marco In" option to "This Workbook" then select a macro from the available list. If you close the Assign Macro dialog and open it again, notice how it appends the name of the file to the macro name. Later, in an automated process the xls filename is appended with a value and I think this is causing the problem because the appended data is static and thus no longer is in sync with the new (latest) filename. How do I assign macro's to either custom toolbar's or autoshapes so that the assignment is tolerant of file name changes? I tried to delete the appended data but it is appended automatically.

I am also curious why some of the macro's listed in the dialog include a filename and macro name delimited with an exclaimation mark and some are macro name only (with no delimiter).

View 9 Replies


ADVERTISEMENT

Macro Use After File Name Is Changed

Oct 28, 2009

I use Excel 2003. With a LOT of help from this board, I've written some macros for a workbook template that will be opened then saved with another name with an .xls extension. The problem is that the macros transfer data to and from another workbook template. Here's the code that's giving me problems.
Windows("BookA.xlt").Activate
Sheets("Sheet1").Select
ActiveWorkbook.Save
Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("BookB.xlt").Activate
How can I use the macro if I open BookA.xlt then change the name to BookZ.xls for my data entries? At the same time BookZ is open, I may use another instance of BookA.xlt and save that one as BookY.xls. What's the most feasible way to still use the macros?

View 9 Replies View Related

File Error: Data May Have Been Lost

Jan 25, 2008

We have an excel template with numerous VBA and Macros embedded within it, saved on a central drive. I am able to access this file ok, however, when I save it when it is reopened by either myself, or a colleague, we get the "File error: data may have been lost" message. Everyone else within my organisation can open the template, edit, and save and subsequently reopen the spreadsheet without any problems at all.

One thing I have noticed, which to me seems strange is that the files I save that have the "error" message are slightly smaller in size than the ones my colleagues save (350kb vs 417kb)

My IT department have tried unintalling Excel and reinstalling, and also rebuilding my profile, all to no avail. A trainer has sat with me to check I am not using the spreadsheet incorrectly (or differently to anyone else, and this is not the case).

View 9 Replies View Related

File Error: Data May Be Lost

Mar 23, 2004

I know, I know... should've backed it up... there exists no other copy of the file in question newer than it's initial creation two weeks ago (been working on it solidly since).

The data does appear to be there but, rather mixed up... does anyone know of any methods/tools/software that might be able to salvage as much of the data (needs to be in it's original structure as it's a cross match excersice) as humanly possible?

View 9 Replies View Related

File Error: Data May Have Been Lost ...

Jun 28, 2007

I have created some Excel files using Excel 2000 on Windows Server 2003. The Excel version is given as 9.0.6926 SP-3. When I open them using Excel 2000 in Windows 2000 after a few sheet deletions I get the error message "File Error: Data May Have Been Lost" when opening the files. The Excel version is given as 9.0.8948 SP-3 in Windows 2000 (why is it different to the version number in Server 2003? Could this be part of the problem?) It crashes on this line

If Sheets(3). Name <> "template" Then

with the error "Run time error 32809 Application defined or object defined error".

The template sheet is very hidden. When I try and unhide it I get the 32809 error again.

View 8 Replies View Related

Data Lost Opening Text File

Mar 1, 2007

I'm working with a list of different manufacturer part numbers, that must be represented in TEXT format only! The file with manufacturer part numbers exported from ERP system to TXT/CSV formats. When I open it in Excel , manually in "Text Import Wizard" I define Column Data Format as Text.

Examples:
1. part number 3214-4-5 turns to 05/04/3214
2. part number 0005487 turns to 5487
3. part number 223878615654 turns to 2.23879E+11
4. part number 4303.240600 turns to 4303.2406

Is anyway to do it automatically from VBA? I try to record macro , but it does not contain "text format".

View 2 Replies View Related

Lost The File Menue Option That Allows A File "Save As"

Apr 4, 2006

I have upgraded to Excel 2003 and have lost the File menue option that allows
a file "Save As".

View 9 Replies View Related

Lost Format After Running Macro

Jun 5, 2014

I have a summary sheet using a macro to summarise multiple other worksheets. However, when I run the macro, the summary sheet loses all formatting.

For example, in one column of the "target" worksheets, the data is entered on multiple lines within a single cell, using alt+shift. When I run the macro the formatting is lost and the data in this specific range is displayed in one long row in a single cell. Also, font formatting is also lost.

In the attached example, the top row shows the original formatting on the "target" worksheet, but below that is the formatting after the macro has run and this is what shows on the summary.

I need it to be formatted as on the original if possible.

View 5 Replies View Related

Excel File Permissions Suddenly Changed

Mar 4, 2014

I have an the excel book with sheets that are password protected so that the users cannot delete rows or columns.

In each sheet I gave permissions for certain ranges that need to be filled out only to specific users that need to fill out those cells.

The users are located and managed in the Active Directory.

The point is that each user can only update his/her sheet within the Excel book.

It was working for a day or two and then all of a sudden without any changes all the permissions were gone and every user was able to edit every sheet.

View 1 Replies View Related

Copy The File With A New Name And Changed The Target Column

Feb 18, 2009

I had copied the file with a new name and changed the target column to 14 (Column N) and it did not work. I went back to the original file with column 10 and it does not work either.

View 5 Replies View Related

Excel 2007 :: Running Second Macro Causes Formatting To Be Lost

Feb 3, 2013

Running Windows 7, Office/Excel 2007

I have a macro which performs some conditional formatting on a worksheet. Based on the value in cells in one column, if the value meets the criteria some simple formatting is performed (row is colored, font changes for that row etc).

The macro then calls a second macro which performs the same evaluation on the same cells from the same column. If the value meets the criteria (same as previously) it is supposed to change the value of the cell by 1/2.

Both macros work, however the 2nd macro also seems to remove all of the formatting, font changes, coloring etc that occurred from the first macro. I have had the first macro save the active workbook and close and have the 2nd macro re-open the file but it sill overwrites the formatting. If I do not have the 2nd macro run the format changes are saved. I have tried saving the file after the first macro runs with .xls and .xlsx extension and no luck.

View 3 Replies View Related

Undo Option Lost After Event Macro Code

May 23, 2008

I have a sales spreadsheet, that people on this forum have very kindly helped me with by giving me two macros; one to remind users that they need to update the month cell when an order comes in, and the other to automatically put the date in a cell when any cell in that row is changed.

The final thread is here: Message To Remind That Cell Is Mandatory

and the Macro used is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
For Each c In Target
If c.Column = 11 Then
If c.Value = "100 - Purchase Order In" Then
MsgBox "Is the Month In correct?"
End If
End If
If c.Column > 1 And c.Column < 18 Then
Cells(c.Row, 1) = Now
End If
Next c
Application.EnableEvents = True
End Sub

However...

We've now found that we cannot undo anything in these spreadsheets. If, for example, a cell is incorrectly copied or deleted, the only way of undoing the change is to shut down the spreadsheet without saving!

Is this just a by-product of using the time macro (a search on other threads suggests that it might be), and, if so, is there any way of changing it?

View 9 Replies View Related

VB Code For Equal Assignment?

Nov 5, 2013

I have 3 columns in an excel sheet (ID, Signed by & To be checked), and in another sheet, I have names under a column "Checkers".

I need a macro where in if I run it "To be checked" column in sheet 1 should be equally assigned to the "Checkers" in sheet 2 and the rest should be blanks.

find the below sample:

Before Macro:

ID
Signed by
To be Checked

121
AAA

123
BBB

[code]....

View 9 Replies View Related

When Cell Date Is Changed Then Macro Should Run

Jun 25, 2014

The following code works fine but now I want it to work without a userform. SO there is no userform and so no controls.

When the user changes date in a cell then a macro should run and following kind of code should execute.

[Code] .......

View 3 Replies View Related

Run Macro Automatically Once 3 Cells Have Changed

May 21, 2008

how to alter this Private Sub for my code to execute automatically once all 3 cells have changed. At the moment it executes as soon as any of the 3 cells change.

Here's the Private and Public Sub codes:

View 9 Replies View Related

Macro Activates Whencombo Box Is Changed

May 1, 2009

I'm making a worksheet with 64 dropdown boxes.

I would love a macro that activates whenever any combo box is changed, that could tell me which DropDown was changed.

DropDown1_Click() seems to be the only option i can find, but is there a way to not have to create a unique method for each object?

View 9 Replies View Related

Macro Execute When Form Is Changed

Aug 17, 2009

How do I have a macro execute anytime a checkbox, or radio button is clicked on a form? I have a macro that needs to run, but I don't want to put it in the code for every checkbox because it will complicate any changes or additions to the form.

View 9 Replies View Related

Formula To Populate Seating Assignment?

Feb 24, 2014

I need to assign van seating assignments to a client room census. I have a workbook attached. I am hoping a formula can get me there. The only variables are if the cell in column E has TO BE CLEANED or READY in it, then I want the formula to overlook that particular cell. I don't want to assign a seat to an empty room! My workbook should make what I am attempting to accomplish much more clear.

Also, there are either 12 or 15 seats available in each van, but the workbook reflects that.

View 9 Replies View Related

UDF Range Argument Into Array Assignment

May 2, 2008

I having trouble with an intermediate step filling of an array based on the input range data and polynomial degree. The values calculated (xsum and xysum) for the array (F in the code) are accurate, but the function returns #VALUE for all elements.

Function poly_fit(ByVal Xdata As Range, Ydata As Range) As Variant
Dim a As Integer, b As Integer, c As Integer
Dim deg As Integer, num_pt As Integer
Dim Xs() As Variant, Ys As Variant
Dim F() As Variant, XY() As Variant, coef() As Variant
Dim xsum As Variant, xysum As Variant
Xs = WorksheetFunction.Transpose(Xdata)
Ys = WorksheetFunction.Transpose(Ydata)
If UBound(Xs) UBound(Ys) Then
MsgBox "You don't have the same number of X's and Y's"
End

View 9 Replies View Related

Autofill Data In A Column - Assignment

Feb 27, 2009

On Sheet1 I have a small list of names in column A and a number next to the name in column B. This number may vary.

What I want to do is on Sheet2 in column A repeat the name based on the number from sheet1 in column B.

Now the sheet is shared among all of us and macro are out. Is this something that is possible with a function?

View 9 Replies View Related

Random Assignment To Work Roster

Jan 28, 2008

i was asked to make a fair rota and i found this 1 on this website its useful but i need to add to it. if you download this work rota you will understand what i mean
http://www.ozgrid.com/forum/attachme...0&d=1154640274. i need to find out what the formula/macro is used in this work rota ...its exactly what i need but i need to add saturday and sunday to this rota and more duty columns as their is more employees and when i click the button it randomply places employees in different positions.

View 2 Replies View Related

Vlookup Changed Validated Cell Value In Macro

Jul 2, 2009

I have a worksheet containing a cell (say A1) that has a validated list of times (data/validation/list) where the cells in the list contain times in 8 minute increments. The user can select cell A1 and manually select any of the validated times from the list. A vlookup formula on this worksheet uses the content of cell A1 to look for this time in another section of the worksheet. When the time is selected manually, the vlookup formula works properly.

As part of a macro, the macro will change the time in cell A1. I do this in the macro by selecting and copying the desired time from the validation list and pasting it into A1. Cell A1 does change to the new value, however the vlookup formula that uses A1 shows a result of #N/A. If I manually change the time, it again works. I am looking for a way to have the macro change this cell to another time and for the vlookup formula to work as it should.

View 2 Replies View Related

Macro To Change Sheet Name When Cell Value Is Changed?

Jun 21, 2012

I would like to create a macro that will automatically change the name of the sheet any time a value is entered into cell a1. For example, in cell A1 of sheet1 I would input "Hello", and then the tab for sheet1 would be automatically renamed to "Hello".

Here's the twist - the workbook will have multiple sheets, and I want all tabs to reflect the value of a specific cell (a1) in each sheet.

For example:

value in sheet1, cell a1 = "Yes" - corresponding tab name would change to "Yes"
value in sheet2, cell a1 = "No" - corresponding tab name would change to "No"
value in sheet3, cell a1 = "Maybe" - corresponding tab name would change to "Maybe"
etc...

The "tab change" cell would always be cell a1 in each sheet.

View 2 Replies View Related

Trigger Macro When Cells Changed By VLookup

Dec 6, 2013

Colouring calendar entries based on a date match... VBA to colour a cell on each line based on date match

The code works fine if you change the date in the cell and press enter however my date changes are triggered via a Vlookup which the code doesn't recognise has changed so doesn't trigger the macro.

Is there any way to amend the VBA code shown below so that it triggers the macro when dates are changed in column I via Vlookup?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Target.EntireRow.Interior.ColorIndex = xlNone

[Code] ......

View 3 Replies View Related

Macro / VBA To Copy And Paste If Cell Is Changed?

Dec 18, 2013

- A database sheet called 'QAEQUIP' which contains information on the movements of pieces of equipment. This information in in order of Column A which is a movements reference number.

- A reporting sheet called 'Email sheet', which you put in a month and year and using macros it copy and pastes the relevant rows from the database and puts them in date order (e.g. put in October 2013 and it will show all of the equipment moves that happened in October 2013, and in date order).

What my end user would like, is to be able to add text to an extra column on the reporting page which gets fed into the database on the corresponding row. What would the best way to do this be? I was thinking VBA for each row (there aren't that many rows) that says if this cell is changed, copy and paste the cell into the corresponding row / column on 'QAEQUIP'.

View 4 Replies View Related

Run Macro Sequence On Set Cell Changed By Drop-Down

Aug 25, 2008

I have found a macro that should work with a change of a specific cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$7" Then
Call Button
End If
End Sub

However, the code doesn t work as with a change of A7 nothing happens.
A7 equals to result from a drop down list --> A7=A5(which is a drop down list value). The "Button" macro is a combination of 4 macros that get rid of zero values and names in pie charts (maybe this plays any role). The master macro is assigned to an object. The code is:

Sub Button1()
Sheets("report").Select
ActiveSheet.ChartObjects("Chart 140").Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _
True, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False, _
Separator:=" "...................

View 8 Replies View Related

Run A Macro When A Drop Down List Choice Is Changed

Jul 23, 2007

I am using INDIRECT to create a second dynamic drop down list (L1) that is based on the cell contents of a first drop down list (I1). I want to create a macro that will blank the 2nd drop down list ONLY when the choice in the first list is CHANGED. I have the following, but it blanks the second list as soon as the first list is clicked on, rather than when a change is made:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Not Intersect(Range("I1"), Target) Is Nothing Then Range("L1").ClearContents

End Sub

View 2 Replies View Related

Assignment Of Number Depending On Range It Falls Into

Jul 16, 2009

The number in cell M21 falls into one of the ranges from C4 to C15. A matrix number needs to be printed in each cell from N21 to N30. The selection of the number to be printed in column N (under Matrix Assignment) depends on the range it is next to in array A4:C15. For example (as shown), the correct number for cell N21 is 122 because 0.2626 falls between 0.24 - 0.2699, and Matrix 122 corresponds to that range.

View 3 Replies View Related

Dynamic Assignment Of Value To Variables Using Macros And VBA Code

Jul 6, 2012

I have two columns one with the variables names and the other with the values now I have to assign vales to teh variables dynamically using the macros and vba code.

View 7 Replies View Related

Sort Macro Reverses When Anything Changed / Added To List

May 13, 2013

I used the "record macro" to record a simple sorting (high to low) of columns A-F based on values (0-3) in column A. Everything works when I initially institute the macro. However, when I change anything or add a new item at the end of the list, the order reverses and sorting goes from lowest to highest.?

Here is what ended up in the macro:

Code:
Sub SortColumnA()
' SortColumnA Macro
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

[Code]....

I noticed that it only includes a fixed value for the length of the list (row 90). Is there a way to have it increase when anything new is added?

As of now I plan on just making an arbitrary large index (~200 ish) to automatically grab anything new.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved