Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Close Window (X)   
Excel VBA Course
[80% Discount] Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Today!)

Dynamic Chart Range

Is there a way to "Name" a chart range like you can Name a Range in a Tab? I am trying to make a Dynamic chart so that I can define the chart range in VBA. My problem is that I alwsy have to create a chart for proposals I do. Unfortionatley, the date range of the chart is always different depending on the period of performance (AKA Number of Months). I know how to dynamically name a Range in VBA. So "Naming" Ranges is not my hurdle. I need to know if there is a way to use that Named range to create a chart vs resetting the range for each proposal I do.

Any chart experts out there???

Thanks for the help!


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(80% Discount Ends Soon!)

View Course




Similar Topics







Regarding Charts in Excel:

Is there a way to have the Min and Max values adjusted dynamically for the Scale of the values being displayed ??

I know that I can use named ranges to display various sections of data -- month by month, or quarter by quarter, for example.

But when the value of these ranges vary greatly from section to section I end of having to manually go and adjust the Min and Max values of the chart scale.

For example, if I was looking at a graph of the S&P 500's prices last November, a Min and Max range of 750 to 1200 would be fine. But if I had a dynamic range established and scrolled over to view the S&P 500's prices for this past February, I would need a Min and Max range of around 650 to 850.

Is there a way to have these Min & Max values adjust automatically depending on the values being displayed ??

Thanks

StanSz


Seemingly super simple, but I can't figure it out.

When I create a bar chart, the bars are horizontal. I want the chart bars to be vertical. It tried to rotate the chart so that it is vertical, but the "rotate" options are greyed out.

How do I get those bars vertical?


I need to create a bar chart for the year. I've already have 3 bars for each of the months, a plan, an actual, and a last year. I need to be able to split out just the actual bar, so that it is a stacked bar. The other two bars need to stay and remain unstacked. Is this possible?


I have a pivot chart with a bunch of data series, and every time we
include/exclude one of the series to see how the chart changes, all the
colors change and we have to spend time re-identifying which series is
which, using the legend to check the colors.

Is there a way to "lock" the colors, so that the bars stay the same colors
regardless of whether other bars are included/excluded?

Thanks!
Keith





I'm trying to separate bars inside a bar chart into separate groups. Adjust the gap applies to all bars. How will I be able to achieve what I need? Please help and thanks


Corporate edict.

I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column "MS96A".

If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*).

What I am looking for is this. If the user selects that cell again, they will get the usual pop-up message, "The cell or chart that you are trying to change is protected..."

I think I am close, but I am getting an "End If without block If" error on the If Clause.


Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MRange As Range
Set MRange = Range("MS96A")
' If Not Intersect(Target, MRange) Is Nothing Then For Each cell In MRange Sheets("Sheet1").Unprotect Password:="temp"
cell.Interior.ColorIndex = 3
cell.Font.Color = vbBlack
Selection.Locked = True
Selection.FormulaHidden = False
Next cell
ActiveSheet.Protect Password:="temp", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub


Hey everyone,

I'm really hoping someone can help me with this...

I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....
Can someone please offer a suggestion for how to do this? I would really appreciate it.
Also, would it be possible to link data from other sheets in the workbook into one single chart?


I have a workbook that has recently changed on me and is causing a lot of grief. I have a lot of named ranges that the scope of the named range has changed to a new sheet. This is creating a lot of problems! Is there any way to manually change the scope of a named range? without deleting the nmaed range and recreating it?


Hello,

Firstly i haven't used excel to a great extent since my college days. So i'm having to re-learn 99.9% of everything i once new...

I am volunteering for a non profit organization and trying to create a reservation system for the rooms that they have - kind of like hotel software, but in excel (i did a similiar thing in college but for plane seats)

Please find it attached.

What i need it to do:

Copy all of the info from the main page to the guest lit (a new row each time).

From the guest list to the Gannt chart - i did a few tutorials on dynamic gantt charts using conditional formatting but cannot get them to work when based on data on a different worksheet.

Finaly is there a way to check for availabilty on any given date?

if just someone could point me in the right direction, i would be appreciate it so much words can't express!!!

Best Regards,

Jamie

P.S for the calender drop down on the main page, i'm using 'microsoft time & date picker 6.0)

http://www.2shared.com/file/6521961/...ibsV05b32.html - pop ups on this site

http://www.easy-share.com/1906519167/RibsV0.5b3.2.xlsm - same file, better website

I have been using Excel (XP) to make a text chart for several months. Some of
my text entries are rather lengthy. The past two weeks these lengthy entries
are showing up as pound signs (#########) when I click off the cell. I know
the text will fit in the cell, and the problem isn't solved by making the
cell bigger or using a little bit less text. I have the cells formatted as
"text" and "wrap to fit". I have printed the pages and the printed version
also has pound signs. I just want my text to show up!



Good day... I need an IF Function that will allow me to action a time in a time range:

... If the time 04:16 falls in the time range 04:00 - 04:29, than put a one (1) in the filed x...
... If the time 04:16 doesn't fall in the time range 04:00 - 04:29, than leave the x fiel empty

Any help is appreciated.


Hi there! This is definately a quick question, but I need to select a range. I'm looking to do so along these lines:
Code:

Range("Activecell.End(xlDown)", "Active.End(xlToRight)").Select


This is definately a problem of not knowing the right jargon to do so.

Could someone please assist?

Thanks in advance!
final


Hi,

This is a very basic question, but i have been struggling for hours, so I must ask.

I I trying to simply fill a combobox on a userform from a named range on a sheet, I want to populate the combobox as soon as the userform starts. (I launch the userform from a button on the sheet) Where must I place the code? and will this code work?
Code:

Sub Button1_Click()
Set UserForm1.combobox1.List = Sheets("Sheet1").Range("MyRange")
UserForm1.Show
End Sub


I am very new to vba, so please excuse my ignorance,
any help would be greatly appreciated

regards
jim


I want to Sum Column "H" starting form "H2" all the way down (rows may vary)
Then Paste My answer in "AM1"
This is what I have so far and for some reason is not working.
Code:

myRange = ActiveSheet.Range("H2", Range("H2").End(xlDown))
Range("AM1") = WorksheetFunction.Sum(myRange)


Any Ideas?


I am trying to sum all numbers <0 using the SUMIF formula in Excel 2003.

FORMULA: =sumif(range,criteria,sum_range)

I got it to work, but I have cell ranges to total that are not next to each
other (such as A5:A15 and C5:C15).
When selecting the ranges for the formula, Excel writes them A5:A15,C5:C15

because of the comma (,) between the cell ranges Excel is reading the A5:A15
as the range and C5:C15 as the critera. How can I get this formula to allow
me to select multiple cell ranges?

All help is appreciated.

Thank you.



I am looking for the code to select the visible data after applying a data filter. Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!

I am using below code to Select the Visible rows in the target range:

Code:

Range("A:p").SpecialCells(xlCellTypeVisible).Select


Problems in this code a

1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.

2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.

E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6

3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.

Some one please revert with the solution.
Thanks in advance.


Hello all,

Until now I have been able to find all my answers through searches. As a VBA novice, it has been very helpful. I am stumped on this one, however. I am trying to autofill from the selected cell in Column C down. I would like it to stop at the last cell with data in Column B. This is the code I have so far:

Range("C2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))

This does the autofill, but doesn't stop at the last cell with data in Column B.

In the past I have use this code to acheive similar results:

Dim endRow As Long
endRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & endRow)

The problem with this code is that I will not always be starting in "C2". I need code that uses whatever the selected cell is.

All help is appreciated. Thanks!


Hi all.

I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.

I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.

I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.
Code:

Sheets("WELCOME").Select
    Range("A1:N18").Select
    ActiveWindow.Zoom = True


By repeating this code for each worksheet, I can make each one be zoomed just right.

However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.

I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.

Anyone know how to do this ?


I'm using some basic code below in an on Workbook Open event to format cells with a value less then 2 and less than 1 with a particular color.

The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks!

Code:

 
Dim myRange As Range
Dim cell As Range
Set myRange = Range("V6:V50000")
    For Each cell In myRange
    If cell.Value < 2 Then cell.Font.ColorIndex = 5
    If cell.Value < 1 Then cell.Font.ColorIndex = 3
    Next





Hi,

How would I concatenate a large range of cells, most of which are blank, and add a comma between each cell's value?
My range is E2:Y2 (I will be filling down), but most of the cells are blank. For example, if I F2=9, I2=11 and X=25, I would like to display them as: 9,11,25.
If in the next row, G=10, K=15 and Y=27, they should look like this: 10,15,27.

Thanks in advance!

Jenny