return value based on combo box selection
I have a combo box (cmbShipDate) in a form that is based on a table with drop
down values of ShipDate1, ShipDate2, ShipDate3, etc. I have created a text
box and I want the correct ship date to be displayed based on the combo box
selection. These ship dates are all stored in a table called tblOrder. I
thought I could use the DLookUp fuction, but I'm not doing something
I don't understand what you mean by "based on a table with drop down
And when you say the "ship dates are all stored in ... tblOrder", do you
mean tha...Type mismatch error in Excel
I receive a type-mismatch error when compiling. The cells being
referenced only have dates. Can someone help? Heres some of the code
a = Range("D:J")
b = DateValue(Format(Now(), "mm-dd-yyyy")) - Weekday(Now(), 1)
c = Application.VLookup(b, a, 2, False) "This is where the ERROR-13
'Tpye Mismatch occurs'
The default property for a range is the .Value property, so
a = Range("D:J") is returning an array of values, not a range object.
Try something like:
Dim a As Range
D...Creating a combination chart using cluster column & stacked bar t.
I need to create a combination chart, using cluster column and stacked bar
types. I need to "cluster" two columns side by side without space in
between, with stacked bars within each of these columns. Then, have space
along the x axis, and have 2 more columns clustered made up of stacked bars
I am charting 2 years side by side, with different types of data within the
columns, so need to stack/segment the column.
How do I create and combination chart, and how do I select these 2 types to
If you stagger your data, you can create sid...Display/Hide a Form Control/Field Based On Another Field Value?
Hi all, I have to Combo boxes on a form. The first one I would always
like users to see. The second, however, I would like hidden if the
first box has certain values selected. So, if ComboBox1 has values of
1 or 2, I would like ComboBox2 to show as normal. If ComboBox1 has a
value of 3, I would like ComboBox2 hidden. Is there a relatively
simple way of doing this, if at all? Thanks for your help as always!
You have to take care of 2 things:
(1) When you move from record to record and the combo box contents changes
(2) When the user changes the contents of the combo box
For (1), inse...Can I add a baseline value to a chart?
I have a chart that shows results of tests taken on different dates to show
improvement. I need to add a baseline to the chart showing the Desired
From an earlier reply:
You can add the lines to the chart by using extra data series.
Here is a collection of examples.
Bernard V Liengme
remove caps from email
"Jeanine" <Jeanine@...Chart automatic sum
I have a huge list of invoices in an excel table, and would like to create a
chart that shows the cumulative number of invoices sent by month. What is
the best way to do this? When I create a chart with the "Invoice Sent"
column highlighted, that data becomes the data range, but what I actually
want the data range to be is the row count of invoices sent. Also, when I
try to switch the X and Y axes so that invoice sent date is on the X axis, it
gives an error because there are too many values (max of 255). However,
there should only be a few dozen x-axis values when I'...Changing positive values to negative
I’m using excel 2007with windows xp pro. I use excel very infreuently and
not since upgrading to 2007.
I have a long column of dollar values that I need to change from positive to
negative values such as:
I can accomplish the feat manually over many hours by F2>.HOME>- > ENTER>
DOWN ARROW and repeat and repeat and repeat. Or I can use a macro. But
using a macro has changed from years ago. When I tried the macro inserted a
specific value of cell rather than the value of the selected cell. So that
didn’t work. What can make this conversion easier?
If t...Cant Type Message in Outlook
Some time I am able to type in outlook and other time it
will not work. I dont know that is going on or if there
is something that I do to bring it on. I was answering
my email this morning and when I got to the last one i
was not able to type. Every key stroke it would ding at
me. It did this to me before and the problem just went
away. I have tried to reboot my computer and that didnt
work. I have no idea.
...Problem with .Values
I have a Sub for construct graphic but
if the number of vx is too large, ERROR
at .Values = Y. Why ?
Thank you for your help,
Dim X() As Double
Dim Y() As Double, i As Long, vx As Double
ReDim X(i), Y(i)
Application.ScreenUpdating = False
ici = ActiveSheet.Name
For vx = -15 To 15
'If the number of vx is too large, ERROR
'at .Values = Y. Why ?
X(i) = vx
Y(i) = vx ^ 2
i = i + 1
ReDim Preserve X(i), Y(i)
Cha...adding values from 2 columns
I am trying to compose a personal cash flow chart where I can enter outgoings
and income for every day of the month and see what the balance is on any day.
I need to get a value in Col F which adds the value in Col C (daily income),
subtracts the value in Col E (daily outgoings) and shows the balance.
Also I may have to specify several rows if I have multiple outgoings on a
Any ideas gratefully received.
Sounds like you want a checkbook register.
Something like this?
A B C D E
Start Bal $1,023.32
Date Item Expense Deposit Balance
$23.3...returning vlookup values for blank cells
I have a spreadsheet that lists "soccer players" by name
down the first colunm and "time in game" across the top
and the position they play in array.
I then use vlookup for another spreedsheet by "position"
down the first column, time across the top and puts the
players name into the positions. All this works fine.
Since there are 5 more kids than positions, the orginal
spreedsheet has blanks when the kids are out of the game.
How do I use vlookup or other to extract the 5 sub'd out
kids at the bottom of the 2nd spreadsheet? It only
returns the nam...How to ignore zero values when plotting a graph
Using Excel 2003. I have a data range for a graph. The values in the cells
are the results of a simple If function - If(m28>0,n28,0). The results are
taken from a larger data input exercise. But, the graph line (a simple
graph!) plots the FALSE value (0) when I would like there to really be no
value & hence no plotted point if the result is FALSE.
This results in the ugly #N/A error in the cell, but it makes the chart
ignore the point. Debra Dalgleish shows how to hide the ugliness with
conditional f...I want to format a cell based on an adjacent cells value
I would like a cell automatically formatted in the same way a conditional
format works - but the format to be based on the value of an adjacent cell.
Any ideas as I cant work out how or if I can achieve this
Cumbo, Use Conditional Formatting, but change cell valve is to formula is
and use something like this, will change the cell if A1=25
=$A$1=25, so if you select B5 and put this in conditional formatting cell B5
will change when A1 = 25
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on an...posting values
on excel,i've been using v-lookup to get values in the same row from one
column but i was wondering if it was possible to use data in 2 columns and
get a value in the same row to transfer into my data sheet. I looked at
every function in the program but can't seem to find the way to do that. if
anyone knows how to do this, pls respond to this. thanks-lisa
VLOOKUP can match a value in the first column and return the value from any
other column - and the same row as the matched value - just expand the
second range to include the data that you want to have returned, and use t...Save spreadsheet using cell values as filename
I am trying to use a command button to save a spreadsheet using cell values
as the file name from the tab Master. For example I want to use cells B2
(Tony.Dungate) and F2 (2010.March) to create a file name of
Tony.Dungate.2010.March.xls. Essentialy I would need something like :
Can someone advise me on the correct code to do this. I have been trying to
use the following without success:
Const sRoot As String = "V:\Database Logs\"
.SaveAs Filename:=sRoot & _
....Cut X axis
Bom dia = Good morning
I need to know, how can i put a variable like "A3" the value of de chart cut
de X axis.
Obrigado = Thanks
Something like this, which is shown for the Y axis, but would work for
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
> Bom dia = Good morning
> I need to know, how can i put a variable like "A3" the value of de chart cut
> de X axis.
> ...Find Value; Return Row number
I was wondering if someone could help me with the following problem:
"I wanted to make a macro which would find the first value (counting from
a1) that matches the value stated in C14 of "FORM". When it gives a match I
want to return the row number to "Sheet2".
I tested it with dates of which I was certain it had to find a match (as I
entered them myself in "Sheet1") Still it returns:
"Named Argument not Found" (Runtime error 448)"
I hope someon sees my mistake(s). Thanks in Advance!!
l = Sheet...How do I turn the pivot chart into a list with all cells filled?
I have the pivot chart and would like to copy and paste it so that all fields
Select the pivot table.
Select the cell where you'd like to paste the copy
Choose Edit>Paste Special
Select Values, click OK
There are instructions here for filling the blanks:
> I have the pivot chart and would like to copy and paste it so that all fields
> are filled
Excel FAQ, Tips & Book List
...change cell values from an edit box
Is there any way to change a cell's value with an edit box? I've used
drop down lists to change/update values, but I have no experience at
all with edit boxes. Any help would be greatly appreciated.
I want a chart with one set of columns and 3 lines. But I
can only get 2 columns with 2 lines. Is there any way to
have an odd number of columns and odd number of lines at
the same time? Can anyone help.
Simply select one of the column data series and then change the chart
type. (Right click and choose Chart type.)
> I want a chart with one set of columns and 3 lines. But I
> can only get 2 columns with 2 lines. Is there any way to
> have an odd number of columns and odd number of lines at
> the same time? Can anyone help.
http:/...Format Chart using excel object model.
I want to create bar and line charts using excel object model from VB. I
able to create charts but problem is formating. I want two things very
1) How to change the color of bars in bar chart and change the color of line
in line chart.
2) How to show data labels.
Waiting for your reply.
Here is some code to give you an idea of the code needed.
These examples where modified from code produced using the macro
recorder in Excel.
ActiveChart.SeriesCollection(1).Interior.Color = RGB(255, 0, 0)
ActiveChart.SeriesCol...Type mismatch 04-02-10
Can anyone help? I am getting "Type mismatch (error 13)" when I try to call a
method. The method is on a class module. The call is from a userform sub.
Dim DataSource as Leaf ...
Set DataSource = new Leaf
.... various properties of DataSource are used successfully ...
.... then the problem call ...
DataSource.Insert 1,"^" <<. msg occurs trying to execute this line
In class module "Leaf" ...
Public Sub Insert (ipOrdinal, ipSubValue)
.... various statements
.... call to another met...how to count if the value falls between a date range
1/2/2005 = 2
2/2/2005 = 2
I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and
similarly 2/1/2005 and 2/3/2005
=countif(a1:a10,">="&date(2005,1,1)) - countif(a1:a10,">"&date(2005,1,3))
(I'm not sure what between means--include the end dates or not???)
> 1/2/2005 = 2
> 2/2/2005 = 2
> I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and
> similarly 2/1/2005 and 2/3/2005
...paired bar chart
Is it possible to create a paired bar chart in Excel?
please define paired bar chart?
> Is it possible to create a paired bar chart in Excel?
See Jon Peltier's site on Tornado Charts
> Is it possible to create a paired bar chart in Excel?
...Change legend text in Excel Chart
Dim appAs Excel.Application
Dim chart As Excel.Chart
Set app = CreateObject("Excel.Application")
chart.HasLegend = true
My problem is how do I change the label of the legend used in the
excel.chart ? Can someone show me how to access it ? thanks.
You use the Name property of the SeriesCollection method like so
This will set the name property of seriescollection 1 to the value test
Chart.SeriesCollection(1).Name = "=""Test"""
Not sure if Excel will like you using the variable Chart??
Microsoft MVP - Excel