|
Create a Bar Chart With a Macro in Excel
Create a bar chart in Excel with this macro. You will be able to quickly and easily turn any range of numbers and data into an Excel bar chart. This is a great macro because it is so simply and therefore easy to integrate into any existing projects of macros for Excel.
This macro has a hard-coded (static) range as the reference for the source data in Excel. This is the only part of the macro that you will need to modify to be able to use the macro in your workbook. To use this macro, simply replace 'Sheet1'!$A$1:$B$67 with the range and cell references of the source data in your Excel workbook.
Where to install the macro: Module
Excel Macro to Create a Bar Chart
Sub Create_Bar_Chart()
'create a bar chart in excel with this macro
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1'!$A$1:$B$67")
ActiveChart.ChartType = xlBarClustered
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Hello:
I wanted to create a macro that would create a control chart from an Excel data set I have which shows survey score information for individuals during a time period.
The data set has 3 fields: persons name being evaluated, a survey score, and the year/month that survey was received. (Note that multiple surveys can happen in a given month).
I would like to be able to run a macro that will evaluate the data, by individual, and create a control chart showing their score, upper control limit (UCL), lower control limit (LCL) and mean, by month. The title would need to show their name and the date range of the surveys being evaluated.
I looked at some off the shelf software and macros, and even those would require a lot of manual input to create them, so I think a customized macro is the way to go.
I would greatly appreciate it if someone could provide some guidance and asssitance towards doing this.
Many thanks in advance-
Ebachenh
I want to create a pivot table chart using a Macro in excel. The problem that I am having is formatting the chart. I can't get it to adjust the size shape and position of the chart when i am recording the macro. Any suggestion?
Hi guys,
Good day. I am trying a macro which i found online which finds the availability of a sheet, delete it if its found an can create the same sheet again.
Sub test_delete_add_sheet()
Application.DisplayAlerts = False
Dim sh As Worksheet, flg As Boolean
For Each sh In Worksheets
If sh.Name Like "Sheet1*" Then flg = True: Exit For
Next
If flg = True Then
Sheets("Sheet1").Delete
Else
Sheets.Add
ActiveSheet.Name = "Sheet1"
End If
End Sub
I wanted to do the same to find a chart and deleting it and creating it if an user specifies it in order to save space in my spreadsheet. Can anyone advice me on this?
I've been using this code for a while to easily create a new chart.
However just recently has stopped working. It now created a "default"
excel chart as a new tab in the workbook. Any suggestions?
'********************************************************************
'Creates 2 data series horizontal bar chart
'********************************************************************
Dim myShtName As String
myShtName = ActiveSheet.Name
Charts.Add
ActiveChart.ChartType = xlBarClustered 'Creates Stacked bar
ActiveChart.SetSourceData Source:=Rng, PlotBy:=xlColumns
'References sheet and and cell range of data to generate chart
ActiveChart.Location Whe =xlLocationAsObject, Name:=myShtName
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
'********************************************************************
'Specifies placement and height & width dimensions of chart
'********************************************************************
With ActiveChart.Parent
.Top = ActiveSheet.Range("F22:Q22").Top
.Left = ActiveSheet.Range("F22:Q52").Left
.Height = ActiveSheet.Range("F22:Q52").Height
.Width = ActiveSheet.Range("F22:Q22").Width
End With
Hi!
I'm currently working on a class project. The project is to create a personal accounting system. I will allow users to insert expense information on a sheet. The user will then hit a button that will drop the information into an output section. I would like to create a pie chart for all the expenses made. I got the pie chart to work after recording a macro for it. However, the pie chart only works for the information already on the output list. It does not work after the user inserts a new expense information since it drops the total expenses downward.
Any recommendations on how to make it work after the user inserts new information into the expense sheet?
Thanks!
Hi All,
Anyone to help me ?
In my XL File, I have 15 Sheets
I have created a form that contain:
- 1 Select List (Selection of Name (1 of the 15 Sheets)
- 1 Select List (Selection of 3 Choice (Linked to Column D, E & F)
- 1 Button (validation)
Could anyone help me with the Code ??
For Example:
Sheet 1 = TATA
Sheet 2 = TETE
Sheet 3 = TOTO
Sheet 4 = .....
For all Sheets, in Column D Values are Sum
in Column E Values are Delta
in Column F Values are Percent
From the Form, if I select "TATA" & "Percent", then after clicking the Buttun, It generates a Chart with data from Column F in Sheet TATA.
Regards
RV
I've been using this code for a while to easily create a new chart.
However just recently has stopped working. It now created a "default"
excel chart as a new tab in the workbook. Any suggestions?
'********************************************************************
'Creates 2 data series horizontal bar chart
'********************************************************************
Dim myShtName As String
myShtName = ActiveSheet.Name
Charts.Add
ActiveChart.ChartType = xlBarClustered 'Creates Stacked bar
ActiveChart.SetSourceData Source:=Rng, PlotBy:=xlColumns
'References sheet and and cell range of data to generate chart
ActiveChart.Location Whe =xlLocationAsObject, Name:=myShtName
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
'********************************************************************
'Specifies placement and height & width dimensions of chart
'********************************************************************
With ActiveChart.Parent
.Top = ActiveSheet.Range("F22:Q22").Top
.Left = ActiveSheet.Range("F22:Q52").Left
.Height = ActiveSheet.Range("F22:Q52").Height
.Width = ActiveSheet.Range("F22:Q22").Width
End With
I've been using this code for a while to easily create a new chart.
However just recently has stopped working. It now created a "default"
excel chart as a new tab in the workbook. Any suggestions?
'********************************************************************
'Creates 2 data series horizontal bar chart
'********************************************************************
Dim myShtName As String
myShtName = ActiveSheet.Name
Charts.Add
ActiveChart.ChartType = xlBarClustered 'Creates Stacked bar
ActiveChart.SetSourceData Source:=Rng, PlotBy:=xlColumns
'References sheet and and cell range of data to generate chart
ActiveChart.Location Whe =xlLocationAsObject, Name:=myShtName
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
'********************************************************************
'Specifies placement and height & width dimensions of chart
'********************************************************************
With ActiveChart.Parent
.Top = ActiveSheet.Range("F22:Q22").Top
.Left = ActiveSheet.Range("F22:Q52").Left
.Height = ActiveSheet.Range("F22:Q52").Height
.Width = ActiveSheet.Range("F22:Q22").Width
End With
Hi all,
I have a major spreadsheet with values sorted in columns.
- Row 2 holds the headers for each row.
- Column A holds dates, and the following columns hold values for each date.
- I have values based on dates in aorund 30 different columns.
I am creating charts with dates on x axis and values on y axis. In order to do this I have created names for each columns. For column B it looks like this:
=Sample!$B$3:INDEX(Sample!$B:$B,MATCH(9.9999E+307,Sample!$A:$A))
The issue is that I have to create separate names and charts for each column, place them in different sheet to get an overview. To many charts.
Allthough this works OK, I would really appreciate a macro that when run it will ask for a headername, and by entering one, the linechart would be created.
In this way I can display only the charts i am interested in that particular day.
Hope someone grasped my problem and are willing to help me in writing out such a macro code
Gnoke
|
|
