I am creating an invoice form in Excel 2003. I like to know if it is possible to increase the value of cel A1 by one everytime a new sheet is created.
For example i created my first worksheet and gave an invoice number 001. Now I create a new worksheet by right clicking on the tab and choose copy/move. I like the second work sheet change the invoice number to 002.
I recently upgraded from Excel 2003 to 2007, and the worksheet change procedure that i have embedded in my worksheet no longer fires when the criteria are met. If and if I fix it to work in 2007, will it still work in 2003?
Here is my procedure:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Excel.Range
If Not Intersect(Target, Range("C19:R19")) Is Nothing Then
For Each rngCell In Intersect(Target, Range("C19:R19"))
If rngCell.Value = "BLACK" Then
MsgBox "Please select a shading style", , "Shading Style Required"
End If
Next rngCell
End If
End Sub
Im searching for a script to help me insert the current time value when a value within the row is changed. For example, if a value changes within the specified row, i.e. B1 changes, then within B12, the current date is inserted. However if B1 does not change then the old B12 value (old last change date) stays the same.
View 9 Replies View RelatedI am completely new to Macro's and VB and the macro below has been generated using the "Record Macro" function in Excel 2013 with a couple of very minor modifications based on some research I have done (hyperlink & input box). My ultimate goal is to make a copy of my "TEMPLATE", which is hidden and the copy could have a variety of names, then create an entry in my "SUMMARY" table that references cells on the newly created sheet. The new entry on the SUMMARY page should be entered in the next available row ... at the moment I need to make sure I have my cursor in the right place before I run the macro. I also want the first cell in the new "SUMMARY" row to create a hyperlink to the newly created worksheet.
The macro does what I need it to do, as long as I name the new sheet "Test", what I would like is for the Macro to recognise the name of the new worksheet and create links to that name. The rows and columns in each new sheet will remain the same, hence the R##C## part will always work.
The "SUMMARY" and "TEMPLATE" worksheet names will not (ever) change.
I've written a little VBScript that generates several hundred Excel 2013 worksheets. In order to protect the users from themselves I protected certain cells. Everything works as expected, except when I discovered that the password I used to protect the worksheet doesn't work to unprotect it. If it matters, I'm not trying to unprotect programmatically, just opening Excel and going that route.
Here's a snippet of my code:
objWS.protect Password="abc123"
I have a bit of code that calls a formatting sub depending on which cell is modified. It is triggered by the Worksheet_Change event, determines which cell is modified, and either calls the formatting sub or doesn't based on the location of the modified cell.
Some of the columns in the sheet have data validation with drop downs. If I select a value from the drop down, it doesn't trigger the Worksheet_Change. If I type a value into the same cell, it does.
This was apparently an issue in Excel '97, but supposedly fixed in '03?
Excel 2003
If user changes cell A1 then give msg box "do not edit this cell" and then change it back to "Quarter". I tried using lock and protect sheet but it limits the other cells. So a msg box and resetting the cell will work better.
I have to analyze our abandoned calls for the year. So far there are close to 2000 abandoned calls and I need analyze them according to the month and the time they were received.
I have two worksheets in my workbook the first worksheet is named DROPPED, this is were all the data is. The second worksheet is called DROPPED CALLS SUMMARY, this is where all the analysis happens.
On the DROPPED CALLS SUMMARY Worksheet I need to have the Sum of Column I IF ColumnA is equal to Jan-13(or whatever month) and Column B is equal to 8:00 AM (or whatever time). I'm using Excel 2003 so I cannot use the SUMIFS Formula and I can't find a formula to work with.
A
B
C
D
[Code]....
I enter 13 digit number in a cell, when I save my Worksheet in CSV format the number is changed.
Example:
I enter 9328627000014 (this is a barcode) After saving this file the number is displayed in the Formula Bar as 9328630000000 and in the Cell it show up as 9.33E+12 When I go to Format Cells, this cell is selected as Scientific. When I change it to Number, after saving it again it returns back to scientific with the old display. Also when I enter 2 digit number in another cell in another column and I choose to be displayed as number with 2 decimal places, after saving the file it returns back as General format. When I am saving this file as CSV a window pops up with a warning that my file may contain features that are not compatible with CSV. I can not choose text format in the cell because it is a datafeed and it has to be read correctly as number.
I use Microsoft Office 2003.
I have a question regarding the properties of an Excel file (.xls 2003). I can change them all (as indicated on Microsoft support website) expect the name of the last person who recorded the document (File ==> Properties==>Stat.)
How to change the name?
I have a very basic understanding of excel, I mean BASIC, for my work I have multiple dates for vehicles to go in for inspections. I would like the cells to change colours when within 30 days, 10 days and 5 days. I'm on a work excel 2003.
View 6 Replies View RelatedI'm creating some userforms for a spreadsheet on Excel 2003 and was wondering if it is possible to have the date field and time field as drop down boxes?
I would ideally like the date field to have 3 drop down boxes (date, month and year) and then the time field to have 2 drop down boxes (hours (in 24 hour time) and minutes).
The date and time would then populate one cell each on the spreadsheet.
I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.
Note: If the column I already have the date and time inserted before then it should give message record already have date and time.
I am using office 2010.
I have a workbook with macro button to run some vba, if i want the button to disable when the user make copies of the workbook (date created is later than original wb saved time) what codes to add in the VBA?
what i assume is to add the time to a cell when the wb is saved, when the wb opens, it check for that cell if the same with the date created, if different, then disable the macro button.
I have an excel spreadsheet that I password encrypted in Excel 2003.
The computer that it was stored on has died however the hard drive was still good and has been added as a second drive in my current computer with no signs of data corruption.
I took ownership of all the files on the old drive and gave explicit rights on my current computer to open that file. However when I open the file with in excel. I get the error: You don't have permission to open this file. Contact the file owner or an administrator to obtain permission.
When I open from windows explorer. I get the error: Excel cannot access 'Passwords.xls' The document may be read-only or encrypted.
And: 'Password.xls' cannot be accessed. The file may be corrupted, located on a server that is not responding, or read-only.
The new computer has Excel 2010 instead of 2003. Additionally, I know the password to the encrypted file but it wont let me even get to a place where I need to put in a password. I cannot move or copy this file to another location either.
I am using excel 2003 and I am looking for a formula that will highlight duplicate cells and change the cell to red.
Unlike newer versions that have duplicate cells within the formatting tool, appears that 03 doesnt.
Also I am looking for a formula that will highlight blue a date cell when the date arrives.
Now iam Working in excel 2003, I got a Requirement that, After Downloading datas From SAP, It Directly Stored in Excel Sheet , Using tht Sheet1, I need to rename all the Filenames at a time, Is It possible, Any code is there to rename,
For Example,
From SAP to Excel Sheet Datas are Like this in Sheet1,
DmsNo Filename
50007685 SDFFG.jpg
50004678 HGJKID.jpg
50003421 VGFTHR.jpg
Then i have rename that filename, rename 'SDFFG.jpg' To '50007685.jpg', and it Should be directly rename to my Source File.
For Example:
Source File: C: estSDFFG.jpg
After renaming it Should be,
Source File: C: est50007685.jpg
any code is there to rename,
Excel 2003
On one of our computers at work, Excel is acting especially strange. The conditional formatting in Excel on one of our computers is not working. In addition, we cannot fill a cell with any color or change the color of the writing. We can click a color for the letters and type it in, but immediately after another cell is highlighted, the writing goes black again. When we try to highlight multiple cells with the cursor, the cells are black instead of the normal color. This happens to not just spreadsheets that are new, but existing spreadsheets on our shared drive from only this one computer as well. No other computers are affected this way. All other computers show the spreadsheets fine and do not have any functionality issues.
I use Excel 2003 & I am trying to design a worksheet to keep track of signs I make & their order numbers. I want the names listed alphabetically. When I add a new name & I arrange the column alphabetically, the columns with the order numbers do not relocate along with the names column.
View 5 Replies View RelatedI have a sheet witch has a number of tick boxes and depending on the response a number of hidden rows may open to allow further info to be recorded, how do I protect the sheet in excel 2003 as unlocking certain cells & protecting the sheet will not work.
View 1 Replies View RelatedI'm trying to copy parts of a worksheet from excel 2003 to word 2003. I've found code that does this alright but I need to be able to re-size the the pasted data to fit the word document. Is there a way to set the properties of the word document like change it to landscape and move the margins etc? Even a simple "reduce the table size to fit the word document". I've included the code i've got already. This code will open up a word document and copy your cells into it but without any useful options. So its ok for a small group of cells.
View 1 Replies View RelatedI have a simple macro that cycles through the sheets in a workbook, and if the sheet's codename matches one of a defined list, some of its data is added to a summary sheet.
The macro works exactly as intended, but a strange thing happened yesterday: some data was missing from the summary sheet because one of the sheets was being ignored. This sheet is named 'MCP' on its tab, and has codename Sheet8.
Here's the strange part: on stepping through the code, cycling the sheets, I noticed that the sheet icon, name and codename had disappeared from the Microsoft Excel Objects folder in the Project Explorer. When the loop got to the sheet in question
Code:
For Each ws In ThisWorkbook.Worksheets
Debug.Print ws.Name
Debug.Print ws.Codename
The above code displayed its name (correctly) as 'MCP', but its codename was blank; the sheet was therefore skipped by the code because the codename had to match against a defined list.
While I was pondering this, and doing some web searches, the sheet then re-appeared in the Project Explorer and everything worked again.
My question is this: is it likely to be due to the workbook being shared? I know that workbook sharing in Excel is often discouraged, but this is a simple workbook only used by a maximum of three users (two of these had the workbook open at the time the issue was reported)
I've changed the code so that the sheet name is inspected in the event that the codename is blank, which should guard against the issue provided sheets are not deleted/renamed.
Windows XP, Excel 2003
I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......
I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....
Currently what i am trying to do is to get the minor files (68 seperate files) to auto populate an area of the master database. I will try to explain it below...
1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').
2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..
3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed
4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.
5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)
6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....
To make matters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red
I've added some code the adds a button to a worksheet
Public Sub AddSheetPrintButton()
Dim btn As Button
Dim t As Range
Set t = ActiveSheet.Range(Cells(1, 6), Cells(1, 7)) ' button position
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.OnAction = "sheetPrint"
.Caption = "Print Sheet"
.Name = "Print"
.Font.ColorIndex = 10
End With
End Sub
This works fine until the workbook is shared!
I'm working on a dynamic payroll spreadsheet that will automatically calculate the overtime worked in a week. Right now, I'm running into a snag. My issue is with the formula in Column R. Right now, as shown below, it is doing the calculation based on regular hours minus 40 to determine the OT time. The snag is very messy and it lay in this: while the row by row calculations for total overtime worked for the week is correct, the sum at the bottom is very much off. I need an accurate method to sum the hours of overtime for the given column.
Here are the guidelines for the pay periods and overtime:
1. The pay periods for the month go from the 1st to the 15th and the 16th to EOM (End of Month). This means that the pay period could end on any given day of the week. More on this in a moment.
2. A work week is defined as Sunday to Saturday.
3. Overtime is calculated based on the rule of anything over 40 hours in a given work week.
4. Holiday hours worked do not count towards the 40 hour mark in granting overtime since Holiday pay is automatically overtime.
If it were just a matter of a bi-weekly (every 2 weeks) pay period, I would simply state =IF(weekday(DATE)=7,Hours_Worked - 40,0), and tag a SUM(range) at the bottom. Unfortunately, with it being a semi-monthly (twice a month), the end of the pay period could be a Wednesday, so a reference to day of the week won't work unless the formula can dynamically determine which set of data to evaluate.
I'm completely willing to toss out the current method of determining overtime. This is the calculations sheet that references a cleanly formatted and designed time card on a tab called "Time Card", so this isn't the full workbook. In fact, once the whole thing is done, this calculation sheet will be hidden.
Columns M and N (which are formula referenced in Column P) are basic End - Start calculations and were hidden to simplify the display as well as the number of formulas displayed.
Column L (formula referenced) is a Yes/No display for if the date in question is holiday pay.
Excel 2003
H
I
O
P
Q
R
S
1
Start Work
Time Out
Day Count
[code].....
Creating a macro that updates and copies part of a worksheet in Excel 2003 and pastes it in MS Word. The sheet I have is a sheet that updates some prices etc and after it updates I usually select a square (part of the sheet), press CTRL+C to copy it and paste it as a picture (paste special ) in word. Is there a way to automate the process?
I am thinking a macro that updates the sheet, copies and pastes iit on word??
I am using Excel 2003.
I have 2 worksheets.
Worksheet 1 is called "Master List Data". Every cell within this worksheet contains a formula so that it matches the cell value contained in a worksheet held within another workbook.
The formula for reference is as follows:
=IF('[CCL Breakdown.xls]MASTER LIST - Active Customers '!A1="","",'[WFX CCL Breakdown.xls]MASTER LIST - Active Customers '!A1) .
e.g.
If Cell A1 on Master List-Active Customer contains no data, Cell A1 in Master List Data would be blank.
If Cell A1 on Master List-Active Customer equals John Smith, Cell A1 in Master List Data would display John Smith.
There are currently non-blank values contained in cells A4:A750. But next week there may be non-blank values is cells A4:A790 (i.e. it will grow each week)
Worksheet 2 is called "Master List Flat. At the moment, I am manually copying and pasting the rows which have non-blank values in cells from Column A from Worksheet 1 into this report (e.g. A4:IV4).
I would like to automate this process and I have created a Macro, but I do not know how to word it so that it will only copy cells with actual data in.
The Macro I have written is below:
Sub IMPORTANDFLATTENDATA()
'
' IMPORTANDFLATTENDATA Macro
' Macro recorded 01/10/2012 by walesb
'
'
Application.ScreenUpdating = False
Sheets("Master List Flat").Select
Rows("4:759").Select
[Code]....
I have a workbook with 120 LOI worksheets that all tie to one master data sheet, and I need to know the date and time of the last change on each worksheet and not the date and time of the last save. The date and time could either be located in a worksheet cell or in the individual worksheet header
View 4 Replies View RelatedI have a worksheet with five columns (A, B, C, D and E)
The cells in Column B contain letters and/or numbers (without spaces) in no particular order.
The cells in Column C contain letters and/or numbers (without spaces) in no particular order.
I want to compare all characters in 1st Cell of Column B with all characters in 1st Cell of Column C, and display the matching characters in 1st Cell of Column D, and the character count of 1st Cell in Column D must be displayed in 1st Cell of Column E. note that multiple instances of the same character must not be treated as duplicates. When execution on 1st Row is finished then repeat procedure for Row 2, etc... Stop execution when first empty cell in Column B is located.
Example:
B1 = LJLM12
C1 = KY2MLK
B2 = ZCG4GM
C2 = X4GGGC
B3 = KTCBNG
C3 = GNBTBB
The script/code/formula must output the following:
D1 = LM2
E1 = 3
D2 = CG4G
E2 = 4
D3 = TBNG
E3 = 4
I am using Excel 2003. Y
Basically the first worksheet (equipment list) is set out for parts used for each individual unit which can be printed out for each unit.
The second worksheet is an equipment list, where each part number and quantity required is displayed which can be sent to suppliers for ordering.
At the moment I
-> copy the equipment list to a new worksheet
-> do an advanced filter for "Unique records only"
-> =SUMIF('Equipment List'!B:B,'Parts List'!A16,'Equipment List'!D:D)
which is much faster than the way we used to do it.
The problem is, when I add a part to the equipment list that is new, I need to go through the process again.
Is there a way to automate so any parts I add to the equipment list, if it is the first time the part number has been entered, it will copy to the parts list worksheet and update the qty column or if it is an existing part number it just updates the qty column?
The equipment list may potentially have up to 100 different drives, but using mainly the same equipment.
I created this sample on an old desktop using excel 2003 but I use 2010 on my laptop.