manually update field
currently we have a contract amount field for a table - many Many
queries,forms, reports are presently using this table- we are manually
recalculating this contract amount - I have created a query to
update-recalculate the contract amount (based new workorders)- BUT!!!! - now
I don't know how to handle past data - using this contract amount from the
table - always query Past - present date - what am i missing- on how to
Also don't know of a good way to find out all of the queries - reports,
forms using this contract amount field - tried object dependencies sa...Pivot Chart Resets Changes
I'm using a pivot chart for the first time and having some difficulty when
the data refreshes. The original chart was an area chart but I changed the
second series to be a column chart on the second axis. However, when the
pivot table refreshes it changes back to an area chart for both series.
What is up?
I've written some VBA code to change it back to the way I want but I'd like
to know if this is typical of pivot charts or if there is a way I can avoid
Thanks. - John
This is a known problem. There's some info in the following MSKB
article, which suggests creating ...Updating 2003 Excel Pivot Chart in 2007
When I go to Chart Toolbar, Design tab, Data/Select Data
The Data Range is grayed out
I can not click on the Collapse Dialog Button
Any help would be greatly appreciated - it used to be so easy and
CA HR User
How can I make a macro which will delete all the Blank rows from a data
before pasting it in Excel
Dim iLastRow As Long
Dim i As Long
Dim rng As Range
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Application.CountA(Rows(i)) = 0 Then
If rng Is Nothing Then
Set rng = Rows(i)
Set rng = Union(rng, Rows(i))
If Not rng Is Nothing Then
(remove nothere from the ...How to maintain consistency with MFC-Macros
I have ever so often shot myself in the foot with MFC macros like
and more. The point is that all of these macros take your class and
parent-class as parameters.
If during development your class hierarchy changes and you forget to
update those macros, things can go wrong in just about any way. Some
cruel and some very subtle.
Now, what I am about is the subtle errors that arise when changing
things like your class hierarchy, because the errors that arise might
be hard to notice and even harder to find (it has already taken me
days...creating forms with multiple tables
I have Access 2002, I created two tables, one that has all our customer
information and the other will put in information pertaining to services
rendered on a customers' pc. My question is, I created a form combining
these two tables. I did it all as one form instead of using a sub form.
When I go to the form to start entering information it will allow me to add
the information for the customer but not the information for the computer.
The table with the customer information has all the information in it, the
table that will have the computer information gets added as the forms ...Sort Macro #2
I recorded this macro to sort a list according to a custom
list. The problem I have is that it does not sort on
another users Excel because they don't have the same
Is there a way round this problem?
Order1:=xlDescending, Header:=xlGuess, _
Application.AddCustomList ListArray:=Range("A1:A10") will add a custo
list to a workbook using a range y...Macro Quandry
I am attempting to run a multi-step If-Then macro. Please see below, it
never gets past the first If statement line.
' Forecast Macro
' Macro recorded 07/27/2005 by D. Baron
If B5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Oct_Forecast"
ElseIf C5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Nov_Forecast"
ElseIf D5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Dec_Forecast"
ElseIf E5 = G1 Then
Ap...import data using macro
I'm trying to import data with exel. I import ten sets of data that are
written in xls format. Here is my method for importing. In the data tab I
press import external data and then press next and then add a space. I do
the same process ten times and was wondering if I could streamline this
process with a macro. Would it be possible to import one set and then teach
the macro to import the next 9 data files in succession. The name of the
files will change every time, but they always are in order (i.e. 718cr1,
718cr2, 718cr3, etc.). The 718 represents today's date, which is...Prevent combo box from updating a table
My application includes a Combo Box which gets it's values from a query, when
I close the form it inserts the value which is in the combo box into the
table from which that data came from. How do I prevent the insert?
On Thu, 11 Mar 2010 20:33:28 GMT, "avtuvy" <u58699@uwe> wrote:
>My application includes a Combo Box which gets it's values from a query, when
>I close the form it inserts the value which is in the combo box into the
>table from which that data came from. How do I prevent the insert?
If the purpose of the combo box is just to find a r...MAcros big Q!
i got a Q, i know pretty much about using the formulas in excel, but
still too rookie using macros, so i got a Q about it.
i made a macro, the result is a graphic char, when i record the macro i
folow all the steps to create the char, and specify the chart has to be
in the same SHEET, i mean not in a new one, and finally i get my chart
and stop the macro.
when i try to run the macro in a new doc, it works, but the chart
appears in a new sheet, anyone knows about that?
***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/fo...I don't have a macro but it says I does
Every time I open my daily active file in excel it says there are macros
when there aren't (see my active file) How do I find out if there are macros
in a file and how do I delete them?
See Debra Dalgleish's suggestions at:
http://www.contextures.com/xlfaqMac.html - NoMacros
"TerryTutor" <TerryTutor@discussions.microsoft.com> wrote in message
> Every time I open my daily active file in excel it says there are macros
> when there aren't (see my active...Define Name use in Macros
I am not sure how or if you can use the Define Name function in a worksheet
to assign a value in an Macro. I am using the command below to assign a value
in the worksheet in the macro. However the user might make changes to the
worksheet that makes this method invalid. If I use the Define Name function
in excel and create a Name can I then us it to assign a value?
Set Total_Assets = Sheets("Balance Sheet").Range("H55")
You should definitely assign a name (Ctrl-F3) to this cell and all ranges
your macros used, in case you or your users insert rows, etc.
Set Total_...Pivot table to calculate variance showing budget and actual.
I know this is rather simple, but I just could not get it right. I have this
Expenese Type Amt Scenario
Staff cost 200 Budget
Admin cost 75 Budget
Office rent 5000 Budget
Staff cost 100 Actual
Admin cost 45 Actual
Office rent 300 Actual
I am able to get the difference using the pivot table "Difference from", but
it only showed that single column. I would like my pivot table to show:
Expenese Type Budget Actual Variance
Staff cost 200 100 1...Opening a file with a Macro
To open files with a macro, I am currently using:
Workbooks.Open Filename:= "C:\file_path.xls"
How can I use a variable as part of the file path?
In other words, before I try to open the file in my VBA code I will set a
variable (let's call it date_extension). Then I want to open a file that I
know will exist with a file path whose final characters before the .xls will
be the date_extension. How can I get the file path to be recognized with the
Thanks for your help
(P.S. I am a total novice, so I may not be familiar with commands or
procedures that are fair...How do i start Macros using IF statements?
How can i use an IF statement to start a macro using data validation to get a
list in a cell if the previous cell equals a specific word.
Eg. IF C1 = "Bedroom" then it will trigger the macro to use data validation
in the next cell to display a list of diffent number of bedrooms which the
user can then choose.
You need a worksheet change event macro to monitor C1 and respond accordingly.
for complete details. Good Luck
"xXx Katie xXx" wrote:
> How can i use an IF statement to start a ma...Check box and make table query
I am trying to write a very basic make table query pulling some of the data
from a form. the form has a check box, [MO] and a default value of -1 (this
is to defaul all records as checked)
If I run a datasheet veiw of the query, the table populates with the "-1"
when I run the make table the field shows ?? and a data type of bianary.
The query is pulling from the form as MOR:[forms]![Frm_RunNew]![MO]
If I uncheck the box it show zero on view and null when run.
I added Cbool to my query and all is fine now.. thanks to anyone who may have
taken t...excel to html looses button and macro, why?
When saving excel to html using mso 2003 I loose my execute button that runs
my macro. I have tried saving sheet, workbook, saving as mhtml. Is there no
way to get the macro module exported into html?
...Edit a table while in form view
I have a form that has multiple tabs and subforms. On one of the tabs is a
subform with a recordsource that is a query. It shows data from a table in
datasheet mode. In that table I have a field "Selected" which is a Yes/No
field (checkbox). In form view, I want to be able to check the checkbox in
the table in the subform. I don't have that field locked, what other
settings should I check?
Can you update fields in the query if you open the query by itself? You
need to determine whether it is the form that is locking the fields or the
...Ho to Delete "Ghost" Pivot Tables
I use Office 2000. I have one spreadsheet that has several sheets with
pivot tables. One sheet has multiple pivot tables.
Today, I suddenly have extra, unwanted, blank (hence ghost) pivot tables
inserted in 2 of my sheets.
I've been using these sheets for about a year with no previous problems.
I have no idea how these "ghost" pivot tables suddenly showed up in my
I can't find a way to delete them.
Question : How do I get rid of them ?
I do have some VB code that spools through the sheets/tables to do a
"refresh". It is not very effecei...pivot table cannot group that selection
I cannot get a column of data to group using a Pivot table. I have done
all the suggestions I've ever know to do in my data which are:
- i removed ALL blank entries
- i did the "text to columns" trick to convert all data to text, then
applied appropriate formatting for the data type.
- i copied all data and pasted special to ensure only the values were
none of this works and i don't know what else to try.
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.Exc...protect macro
how to protect a macro by giving a password
Message posted from http://www.ExcelForum.com
You can protect your project by going into the VBE (Alt-F11 is one way).
Selecting your project
Tools|VBAProject Properties|Protection tab.
That said, there are password crackers out there. But it keeps most out of your
"nowfal <" wrote:
> how to protect a macro by giving a password?
> Message posted from http://www.ExcelForum.com/
Message posted from http://www.ExcelForum.com
...How do I create a combination chart and table with different data.
I am using Windows XP, and Microsoft Excel 2003. I have been asked to create
scorecards with charts displaying rates graphically, and an attached table
underneath with numerator and denominator data. The combination graphs in
the custom charts try to graph and display all the data. Can I create a
combination chart with the rates graphically displayed and the underlying
numerator/denominator data presented in a linked table underneath the chart?
Or do I need to create the graph and table separately?
You would do better to make a separate chart and table, because chart data
tables ...Pivot Tables
I run into a situation i dont know how to solve. The source table looks like
I'd like to count the number of XP workstations by using a pivot table.
OS "count of WKSNAME"
The problem i run into is that it counts the nummer of rows (resulting in 4
XP workstations) where i want to count the Unique rows.
Filtering the source data doenst give the desired result, i cant modify the
source data either.
...how to make a table with 10 million rows in one column?
how to make a table with 10 million rows in one column?
Build a little, test a little.
> how to make a table with 10 million rows in one column?
On Mon, 22 Mar 2010 09:44:01 -0700, pemt <firstname.lastname@example.org>
>how to make a table with 10 million rows in one column?