Remove blank entries from a list box either using VBA or basic formula
I've got a spead sheet that asks the user to enter details of worker
onto one sheet.
ie. Surname.............First Name............Age.........etc.etc.
This data is then used in a few pivot table reports.
One pivot table simply lists the workers full names for entry int
The cells asking for the name are have a validation setting to
The only problem I have is that the list box range is predetermined
but the list may be from 1 names to 150 names.
When entering the name into the new sheet using the dropdown box, i
shows all blanks as well as the names ente...How does excel do Top 10 in pivot
Can any one tell me what logic does excel use to get top 10 or top 5
in the pivot. I am asking this because when you have a time series
data you should ideally have Top 3 for each month or year but it just
gives a list of products , now I wanted to know what basis, is it by
the latest month or the first month.
If I were to...How to protect the query use for a Pivot that uses 'Get External D
I'm geting external data for a Pivot Table using the 'Get External Data'
option, this works fine and retrieves the information from a SQL 2000
In order to don't retrieve "all" the records, I use a filter (ie. where
Company equal to Microsoft), that way the User only sees that amount of data
that he is supposed to see.
The problem I have is that a power User that knows excel will be able to
edit/remove that filter and see "all" data.
Of course I can create different database views (ie. one for each filter)
and apply proper access right...forgot my vba project passowrd
guys I forgot my vba project password for one of my old file.
how do i remove this password
The below link should help.
> guys I forgot my vba project password for one of my old file.
> how do i remove this password
the site talks about some hex editor for vba project passwords. I dont know
whats this and how to do it. any help?
"Jacob Skaria" wrote:
> The below link should help.
> --...Update combo from table
Hi, I am working on a database I did not create, always fun. I have a form
with a combo box on it for client name. There is a button to add a new
client. When you click on it, it brings up the table and you enter in all
the client information. This particular form only needs the client name. Is
there a way, when you close the table, to have the client name combo box
update so the user doesn't have to reload the whole form? What happens is
the user will fill in a lot of information on this form, realize they need to
add a client, do that, then have to re-type everything because...Removing artifacts from a spreadsheet with VBA
I copy excerpts of HTML tables into spreadsheets and get "artifacts" some of
which respond to clicking to select and then using DELETE or CUT. When the
cell/row they're in is deleted they migrate to the adjcent row/cell. How can
I identify and remove these with VBA? I've tried to "Record Macro" to see
what the genie does, but all he says is: "Selection.Delete" and that doesn't
tell me what was selected! So, it's useless to include it in my code. I
want to get rid of them because the really drag in a lot of something that
slows d...MS Access Report Graph Table
I cannot get my report to retain the currency format when previewed. It
looks fine in design view, but does not retain it. I have changed all of the
underlying queries and tables to include currency formatting.
Any help would be appreciated
Check the Format property of the field on the Report.
"I feel I have been denied critical, need to know, information!" - Tremors
"Bev" <Bev@discussions.microsoft.com> wrote in message
news:0D5DBFA2-2CB4-4C2E-AE6A-C5DAE41EC1F2@...Change font for each text box using VBA
The front page of a report has fields that a user can change according to
their needs. Is there a way for the user to specify the font type and size
for each control individually? Possibly for just each section (a couple of
headers and the detail sections)?
Me.FontName = Me.TitleLineFontName
Me.FontSize = Me.TitleLineFontSize
TitleLineFontName and TitleLineFontSize are fields in a table in which a
person specifies the font type and size.
Am I on the right track going that direction?
I'm using Access 2007.
...Table Import error #2
I get this error when I try to open Table Import - 'Open Operation on
Import_Detail failed because path does not exist.' . One suggestion I got is
to check the user permissions on the folder where the import tables are
created, but how do I find what folder that is?
We use GP 8.0.
...How do I put a "green bar look" in my Access 2007 tables?
How do I put a "green bar look" in my Access 2007 tables?
"Cajunguy55" <Cajunguy55@discussions.microsoft.com> wrote
> How do I put a "green bar look" in my Access 2007 tables?
Short answer: you don't.
Long answer: you shouldn't be working directly in the tables unless it is a
personal-use, end-user database; you should be using forms and reports that
display the information. In reports, the "green bar" effect can be
accomplished with code in the report itself.
Microsoft Office Access MVP
...Pivot Table Help #5
I have a pivot table set up and all is ok
i have set up a macro to copy the data in the pivot table and to paste it
sumwhere else and to sort it
all is good, but when i save the spreadsheet and close it down and reopen it
and run the macro the pivot table shrinks down to 2 rows and 2 colums
how do i get my data back on show
Can anyone help
Thanks in Advance
can u pls send your file to me: firstname.lastname@example.org. i hope, i can help you
"Debby Bunce" wrote:
> Good Morning
> I have a pivot table set up and all is ok
> i have set u...inserting multi-page excel table into Word
I'd like to be able to insert a large (i.e. multi-page)
range of data into Word 2000 in one operation and have it
paginate properly. At the moment, I have to cut and paste
one page worth of data at a time onto each Word page and
fiddle with the sizing. Is there an easier way to do this?
...Find the value of a point with VBA
Pardon my english ! I'm french.
I need to find the values of points of a serie (in charts of course)
with VBA. I can't refer to the value of the source cells because of the
very bad organization of the source sheets.
The only way I found is to add datalabels, pick the datalabels values,
convert them to numeric values.
Is there a shorter method to do that ? (XL2K)
I did not find the answer on the french speaking excel newsgroup though
it's very active and frendly.
This will get the values from series 1. Note that linked cells with
erro...Advice: XML file as database or table
If I choose to use a database then I need to use multiple tables. With a XML
file I can select "my objects" as once. I think that there will be around
10.000 records (objects).
I want to select my objects on different (selection criteria) ways (on
attributes). And I want to add, update and delete my objects.
Can somebody tell me if it is a good idea to use a XML file as database or
Can I read parts of a XML file (for paging purpose)? Or do I need to select
everything at once?
...Updating TABLE with Query ( Update?)
I have a table that i would like to update with results of a seperate query
I would like to update
table1.Title with query.title
table1.quantity with query.quantity
where table1.item = query.item
So I had to use maketble query and then update from the temp file to get it
Is that a work around or the way it needs to be done ?
> I have a table that i would like to update with results of a seperate query
> I would like to update
> table1.Title with query.title
> table1.quantity with query.quantity
> where table1.it...2003: Can't start VBA Editor
I'm using Outlook 2003. I started using the Visual Basic
Editor yesterday. Today I can't even open it or get the
macro dialog to display. There is no error, but neither
the editor nor dialog shows up.
I tried using Setup to repair my Office installation, but
that didn't help.
...Redefine Table Array
I already have set up a Vlookup but i want to add more items to this how to i
make the range include the newly added data
I like to put my table on a dedicated worksheet. Then I can use the whole
column without worrying.
But you could use a dynamic range name that grows/extracts with your data.
Take a look at Debra Dalgleish's site to read more about it:
Shaya M wrote:
> I already have set up a Vlookup but i want to add more items to this how to i
> make the range include the newly added da...About pivot tables
I am working on a pivot table that displays data from various dates from
1999 to 2005 and i would like to filter it to show only the data coresponding
to dates up to march 2003. How do i do that? I cant go do it manually from
the date column because the dates are over much to many and if I try to hide
details for months jan to march , it seems that it hides the data for all
years including 2004 and 2005 which i want calculated in the pivot table.
any help guys?
thank you in advance,
Right-click the Date field button
Choose Group and Show Detail > Group
In the End Data...Why is the order of my data table opposite from graph?
I'm using a simple bar graph with a data table. The data table is in the
opposite order of my graph. I've tried resorting the source data, I've tried
reversing the categories on the axis. I keep getting the same result - the Y
axis is in alpha order from top to bottom (which is what I want) but the data
table is in reverse alpha order (alpha goes right to left)
I would first check the formatting of the data to make sure it is formatted
correctly. If there are no data format problems, can you provide an example
of the data that you're trying to chart?
A window is appearing Project Explorer and cannot be removed using the
"Remove Current Window from VBA" Option.
When adding the Window again it appears ad WindowName1.
How is an unwanted windpw removed from VBA.
If the window is a VBA User Form it will need to be deleted from the VBA
If it is a GP window then it is possible that your VBA files have been
Whenever I get weird behaviour in VBA, I export all my customisations as
packages, rename the *.VBA files and custom reports and forms dictionaries
and then import everything back...Table Size Limitations 09-08-05
I am hoping you can help me answer this probably easy question.
I am trying to add some new columns to the contacts table in 1.2 and I
believe I am running into a space limitation. I have added about 550 bytes
of information to the table and then I started getting the error. There
error that I am getting or the system log does not have any information in it
as to what the error is.
I am a little confused as to why I would be getting this error, I realize
that there is a 8000 byte sql limit, but the stock contact table has around
3700 bytes and I have only added 550 bytes. Whe...VBA Code Written Once For All My Forms
I have a couple procedures that I use for dozens of forms in teh same
database. The code for this prodecure is identical. It's not in every form,
but many. Can I reference such a procedure from any form I want, but write
the procedure once?
On Sat, 6 Mar 2010 17:21:01 -0800, VWP1 wrote:
> I have a couple procedures that I use for dozens of forms in teh same
> database. The code for this prodecure is identical. It's not in every form,
> but many. Can I reference such a procedure from any form I want, but write
> the procedure once?
The usual way...access db carry forward balance vs. table design change?
with respect to this post.
i am working on a database to use in a small town for home water usage tracking and billing. the db is structured with a customer info table and a monthly usage and charges table. there is a relationship setup for the home address field, one to many. kinda like this....
i think what i want to do is ca...comparing componants of tables
I cannot easily compare data elements of comparative data. Here's
what I mean,
john smith observation 1 observation 2
weight 160 155
belt size 40 38
shoe size 12 11
bill jones observation 1 observation 2
weight 128 119
belt size 29 28
shoe size 8 9
mary Dee observation 1 observation 2
weight 120 129
belt size 25 26
shoe size 8 ...Suppress Zeros in a Pivot Table
Can I suppress zeros in a Pivot Table? Many of the results are zero, how can
I hide the rows without using a macro.
You could add a column of formulas in a hidden part of the pivot table
worksheet, then use it to filter the pivot table.
For example, if your pivot table is in cells A3:J100, and has two row
fields, enter the following formula in cell R2:
Copy the formula down to the last row to which the pivot table will
In cell R1, enter a heading, e.g. Filter
Select cell R1, and choose D...