Over 1 million tech questions and answers.

Type error when applying Excel VBA chart formatting macro to all selected charts

Q: Type error when applying Excel VBA chart formatting macro to all selected charts

I've been trying to implement a macro to apply a defined chart format to every selected chart. I've been using a post from this forum a few years back http://forums.techguy.org/business-applications/823459-macro-format-charts.html to help, and it's working great for a single chart. However, I'd like to enable it to run across several selected charts at once, and that is giving me problems that I can't get my head around.

I'm trying to use this code to run through the objects in the selection:

Sub ReFormatSelectedCharts()

Dim ThisChartObj As ChartObject

Dim ThisType As String
ThisType = TypeName(Selection)

Select Case ThisType

Case "DrawingObjects"

For Each ThisChartObj In Selection

Call FormatThisChart(ThisChartObj)

Next ThisChartObj

...Click to expand...

However, it gives me a type mismatch when I try to use it. I've investigated by implementing the following code instead of the for each loop:

X = Selection.Count
ReDim arrCharts(1 To X)
For i = 1 To Selection.Count
arrCharts(i) = Selection(i).Name
Debug.Print arrCharts(i)
Next iClick to expand...

Interestingly, it seems that the second chart in the selection is being treated as a button by Excel - so in the debug output in the case with three charts I'll see something like:

Chart 10
Button 1
Chart 5Click to expand...

while for two charts it'll just be
Chart 10
Button 1Click to expand...

I've tried it with different pairs and sets of charts, and even got so far as making sure I called the routine directly from the macros list rather than via a button, but I just cannot for the life of me work out why Excel won't recognise the second chart as a chart object, but instead as a button (running Typename(Selection(i)) similarly returns 'button' for the second chart).

Grateful for any thoughts!

Read other answers
Preferred Solution: Type error when applying Excel VBA chart formatting macro to all selected charts

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)


Excel Masters, I am not sure how to loop through the following code for multiple row formatting. The code works fine if the user has selected cells within a single row. However, when multiple rows are selected, the 'With ActiveCell' formatting only works for the first row selected. How can I loop the 'else' statement to format the ranges for all rows with a cell selected?
Private Sub Button_DeleteRow_Click()
Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then



Selection.EntireRow.Interior.ColorIndex = xlNone
With ActiveCell
Range(Cells(.Row, "AA"), Cells(.Row, "AN")).Interior.ColorIndex = 15
Range(Cells(.Row, "c"), Cells(.Row, "d")).Interior.ColorIndex = 15
Range(Cells(.Row, "a"), Cells(.Row, "a")).Interior.ColorIndex = 15
Range(Cells(.Row, "j"), Cells(.Row, "j")).Interior.ColorIndex = 15
Range(Cells(.Row, "n"), Cells(.Row, "n")).Interior.ColorIndex = 15
End With

End If

End Sub

On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.

A:Solved: Excel Macro - Loop selected-cell formatting for multiple rows

Something like:

Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then



Selection.EntireRow.Interior.ColorIndex = xlNone
SelRows = Selection.Resize(, 1).Cells.Count
Cells(ActiveCell.Row, 27).Resize(SelRows, 14).Interior.ColorIndex = 15

'(and so on)

End If


On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.Click to expand...

If you mean "how do I bypass that for Selection.EntireRow.Delete then:

Application.EnableEvents = False
Application.EnableEvents = True

Read other 2 answers

I have a large excel spreadsheet with over 30 different graphs. Instead of manually updating the charts to include the last 2 years of data I am trying to have all the charts automatically update upon opening. I run into problems when I try to change the formula of the charts' series-I get an error message saying "Unable to set the formula property of the Series class." I have been stuck on this problem for several days and need to figure it out for my internship. Any help would be greatly appreciated. Here are the first two procedures (I didn't think I should include all 30)

Sub SoybeanUpdate()

ChDir "Q:\Research\CSIDATA\allcsicont"
Workbooks.OpenText Filename:="Q:\Research\CSIDATA\allcsicont\S__0361N.TXT", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 5), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1))

Windows("All Commodity Macro Test.xls").Activate
ActiveWorkbook.Names.Add Name:="SoybeanDate", RefersToR1C1:= _
ActiveWorkbook.Names.Add Name:="SoybeanPrice", RefersToR1C1:= _

ActiveWorkbook.Names.Add Name:... Read more

Read other answers

Hi There

Quite new to this. I am creating a macro to use create multiple charts. The idea being that I use column A everytime as the X axis and then columns B, C, D etc as the Y axis. Each graph will only have one series, i.e. chart 1 uses AB, chart 2 uses A and C, chart 3 uses A and D etc etc. I am attempting to use dymanic named ranges. My code is almost there however I have two problems; the first graph is produced fine. The second graph however includes colum B as a second (unwanted) series, the next graph includes columns B and C, etc etc. Why is this as they are not included within the named ranges? Secondly, the first graph comes out with a chart title fine however the subsequent graphs do not. Below is my code...

Sub charts()

Range(Selection, Selection.End(xlDown)).Select
myrangeAREA = Selection.Address

Range(Selection, Selection.End(xlDown)).Select
myrangeRWE = Selection.Address

Range(Selection, Selection.End(xlDown)).Select
mrRWECHA = Selection.Address

Range(Selection, Selection.End(xlDown)).Select
mrtest = Selection.Address
With ActiveSheet.ChartObjects.Add(Left:=100, Width:=327, Top:=75, Height:=229)

.Chart.SetSourceData Source:=Sheets("Sheet1").Range(myrangeAREA, myrangeRWE)
.Chart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="custom1"
End With

With ActiveSheet.ChartObjects.Add(Left:=150, Width:=327, Top:=125, He... Read more

A:Excel VBA macro - multiple charts

Can you post your workbook?


Read other 2 answers

I have a report that my company uses to track defects by supervisor and also by department. Currently I have these on two separate sheets. All the info is the same except for the charts, on one sheet the chart is for the sup and the other sheet is for the dept.

What I am wanting to know is how do I put both of these to one sheet using one chart? Example: If I want to view this by sup only the chart will have that info and if I want to view by dept only the chart will delete the sup info and put in the dept info.

Below is part of the code that creates the chart. I cannot upload my file as it is too big and even zipping it still too big.

This one is for the dept.
Dim DM_Department_CountB As Single
DM_Department_CountB = Range("C3")
Sheets("MONTHLY 8Ds").Select
ActiveSheet.ChartObjects("Chart 3").Activate
With ActiveChart.SeriesCollection(1)
.Name = "='MONTHLY DATA BY DEPT'!R3C1:R" + Format(DM_Department_CountB + 2) + "C1"
.Values = "='MONTHLY DATA BY DEPT'!R3C2:R" + Format(DM_Department_CountB + 2) + "C2"
End With

This one is for sup.
Dim DM_Department_CountC As Single
DM_Department_CountC = Range("C3")
Sheets("MONTHLY 8Ds by sup").Select
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.SeriesCollection(1)
.Name = "='MONTHLY DATA BY SUP'!R3C1:R" + Format(DM_Departmen... Read more

A:Excel macro help for updating charts

Read other 7 answers

one more little problem.

I have this code (which I got from recording a macro) for placing a chart on a page:

ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("FixedPointsBlank").Range("C920"), PlotBy _
ActiveChart.SeriesCollection(1).XValues = "=FixedPointsBlank!R9C3:R20C3"
ActiveChart.SeriesCollection(1).Name = "=""Frequency"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="FixedPointsBlank"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Frequency Plot for Fixed Measurements"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"Upper End Value (cpm)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Frequency"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False

On the 2 lines that have the Range function I have replaced inside the parenthesis with: Cells(30, 8), Cells(lastUEV, 9)
which works just fine. I have to do this because from one run to another the range size will change.

The other line that needs to be changed the same way is:

ActiveChart.SeriesCollection(1).XValues = "=FixedPointsBlank!R9C3:R20C3"

However, when I replace the R9C3:R20C3 with Cells(30, 8), Cells(lastUEV, 9) I get an App... Read more

A:Solved: excel vb macro code for chart

Read other 10 answers

I have a long Macro that runs and within the macro a graph is created (in the same sheet as the data) so now I have an "ActiveChart" that is an XY scatter scatter smooth no markers graph. I want to be able to click three (3) times on this graph and have those points (their x and y value) to be assigned to a particular cell, (we'll say we should put the X value of the first point that was clicked in "AA1" and its Y value in "AB1", then the second point would be in "AA2" and "AB2", followed by the third point going to "AA3" and "AB3". I have no idea how to even start this, but I just have an active chart that is made in the middle of the macro and I want to be able to click on that chart three times, record the data values, then have my macro finish running. Is there a way to do this!? Thanks!

Read other answers

Hi dears,

I stuck in VBA code issue, where i am not able to run my VBA code on other workbooks.

Please find 2 attachments Format.xls & Testpurpose.xls

Format.xls is my personal File where i have stored all macros to run on other workbooks.
Please open Testpurpose.xls and run Macro Format_columns, it is working fine,

after that run Macro Pivot_chart, here is the problem, i am not able to get the pivot table of existing workbook.
it is always creating pivot table taking values of my personal file.

please help in changing VBA code.

A:pivot Chart macro Problem In Excel

Dears can anybody pls respond.

Read other 1 answers


I've just made a pretty decent scheme in my Excel. It's all good but I would like it to fill a whole A/4 sized piece of paper, by increasing the size of the cells as well as the text in them proportionally, so that I don't have any unused, blank cells. Is there a simple way to do this?

Thanks for your help in advance.

A:Microsoft Excel 2010 - chart formatting question

That is likely to be controlled by your printer preferences.

From: File, Print, select 'Printer Properties' (NOT Page Set-up - at the bottom of the menu) and choose your file output format.

BTW: Which printer are you using?

Read other 1 answers

It's deceptively simple what I'm trying to do, but after hours puzzling over Excel VBA forums and tutorials, I'm ready to throw in the towel. I'm using Excel:MAC 2011.

Each row in my sheet represents a year in the life of one job. What I'd like to be able to do is enter an employee's name in column C, and start and end dates in columns F and G. Then I want the script to dynamically pick up the background color for that employee (column D) and copy it into the appropriate start-end range in the calendar in columns I-NJ (autofilled with 2012 dates).

Background colors for each employee are currently set in a key, and I have a script that lifts the appropriate color to each row of data (in column D). This part of the script actually works.
Set oName = Range("C6:C500")
For Each Cell In oName
Select Case Cell.Value
Case "Lynnette"
Cell.Offset(0, 1).Interior.Color = [NM7].Interior.Color
Case "Cheryl"
Cell.Offset(0, 1).Interior.Color = [NM11].Interior.Color
Case "John"
Cell.Offset(0, 1).Interior.Color = [NM9].Interior.Color
Case "Michelle"
Cell.Offset(0, 1).Interior.Color = [NM12].Interior.Color
Case "Richard"
Cell.Offset(0, 1).Interior.Color = [NM8].Interior.Color
Case "Clara"
Cell.Offset(0, 1).In... Read more

A:Excel VBA - modified gantt chart with custom conditional formatting

Read other 11 answers


This is very frustrating for me but probably very easy for someone with the know!

I use excel for accounts and enter the relevant details along a row; 1 row for each transaction. I want to be able to select specific cells from that row and paste them into an Excel invoice template i have.

I have used the macro record button to do this and it works fine except for one thing; it always copies the cells from the row i created the macro from. I would like it to copy the cells from the row i have selected. Hope this makes sense.

It obviously selects the cell (c4 etc) in the code but i want it to select based on the row of the cell selected if you know what i mean.

Here is the basic code i have
Sub CreateInvoice()
' CreateInvoice Macro
' Macro recorded 01/09/2007 by User
' Keyboard Shortcut: Ctrl+i
ChDir "X:\Accounting\Customer Accounts\Invoices"
Workbooks.Open Filename:= _
"X:\Accounting\Customer Accounts\Invoices\Blank Invoice.xls", UpdateLinks:=3
Windows("Accounts September 2007.xls").Activate
Windows("Blank Invoice.xls").Activate
Windows("Accounts September 2007.xls").Activate
Application.CutCopyMode = False
Windows("Blank Invoice.xls").Activate
Application.CutCopyMode = False
With Selection
.H... Read more

A:Excel copy & paste Macro for selected cells

Read other 16 answers

What is the best way to determine through code if a range is selected?

A:Solved: Excel Macro -> Determine if range is selected

For Example, I want to run an IF THEN statement only if specific cells are not the ones the value is being changed or added.

The specific cells do not go in sequence.

Read other 3 answers


My VBA/Macro knowledge is very basic, but I am able to record macros and edit them. I have a scatter plot (with 5 series) and I want to automatically change the line style (i.e. solid or dashed etc.) depending on the text in a cell. This is what I tried, but I cannot get the code to reference the cell which contains the text:


Sub Macro14()
' Macro14 Macro
ActiveSheet.ChartObjects("Chart 1").Activate
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
End With
End Sub

For the line ".DashStyle = msoLineSolid" I tried ".DashStyle = Range("A7").Select" but this does not work.

Is there a simpler way to set-up a macro to format graphs automatically based on a cell content/format/colour, does anyone know a good guide to do this?


A:Excel Macro to automatically change chart line style based on cell text


I'm not familiar with charts. But, see if this helps.


Read other 3 answers

Hey all,

I have come to you in a fit of desperation. My USB drive was stolen recently and my master Excel data file was lost, which contained all my compiled data from my most recent lab experiment.

Anyway, I have charts of all the important stuff in a word document. The data is there, I can hover on each data point and see the values, and the axes and chart are all accessible for editing.

My question is, how the heck do I extract that data back out of the chart into a table or Excel spreadsheet? There MUST be a way that is better than copying down each data point one at a time

Your help is greatly appreciated.

A:Excel chart in Word 2010: extract chart values? Lost Excel data file!!!! Help.

Hi welcome to the fourm.
I don't know it reverse engineering is possible.
Something like convert chart to table?

One thing you could try is copy the chart back to a new Excel file and see if more is possible there than in Word?

Like I said never needed this. Have you googled for something like Excel data reverse enginieering?
Maybe something come up.

Read other 1 answers

I was wondering if it is possible to trigger a macro when an particular custom tab is clicked.
This is part of my XLM code from the Excel file:

<!-- Add Custom group to the Home tab in the ribbon with one button-->
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false">
<[COLOR="Red"]tab id="customTab" label="Inzet Planner" insertBeforeMso="TabHome[/COLOR]">
<group id="customGroup1" label="Planner Maand Selecties" >
<button id="cG1B1" label="Huidige Maand" size="large" onAction="BtnOnActionCall" imageMso="ArrangeByAppointmentStart"/>
etc ....

It all works perfectly for the buttons, that's not the issue.
What I am looking for is that if CustomGroup1 is pressed a macro is triggered. An then of course I have a series of groups and that can be a different macro or no macro at all.
I tried sendKeys and so but that does not work well.
Like the Button Id has an onAction function, is there something similar for when a Group is selected?

Thanks for any answers

A:Solved: Excel 2010 trigger macro when a ribbon tab is selected

Read other 15 answers

Hey everyone,

I was wondering if anybody on these forums could help me with the below task?

I run a backup program that generates email reports to a subfolder in my Outlook 2010 every 6-8 hours to tell me the status of every backup job that I have set up and have running. The email details the job name, the job start time, and the job status (running, completed successfully, failed, missed, cancelled etc).

At the moment, I sift through each and every one of these emails (I have over 100 backup jobs per day) and enter the relevant data manually into my excel spreadsheet. The spreadsheet looks like the following:

Job name Monday 1st Oct Tuesday 2nd Oct Wednesday 3rd Oct Thursday 4th Oct

What I&#8217;d like is to automate this process, so that each day, perhaps every 4 hours, a script would run that would search for certain values within each email I receive and update the excel sheet accordingly.


- Monday 1st Oct, 10am &#8211; email comes into inbox with subject/body containing &#8220;Job_xxx has started &#8220; >>>> update Excel sheet to record RUNNING under column &#8216;Monday&#8217; in row titled 'job_xxx'
- Monday 1st Oct, 1pm &#8211; email comes into inbox with subject/body containing &#8220;job_xxx completed successfully&#8221; >>>> update Excel sheet to record SUCCESS, ... Read more

Read other answers

I have an excel macro that highlights cells based on a number entered from 0 to 100. I can highlight the area I want formatted automatically and run the macro, but any cell with a blank or non-numeric value gets formatted red (or sometimes interpreted as >100). I want to add a fourth condition either at the start or as a final condition to not change the cell formatting if NaN or alternatively format the background as white (no cell background color). Using excel 2003, i understand I am limited to 3 conditions for formatting. I am also unfamiliar with implementing the IsNumber function. Here is the code, captured w/ macro recording:


Sub proquiz()
' proquiz Macro
' Highlight all cells in range according to pass criteria
' Keyboard Shortcut: Ctrl+h
With Selection.Interior
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
, Formula1:="100"
Selection.FormatConditions(1).Interior.ColorIndex = 33
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="86", Formula2:="89"
Selection.FormatConditions(2).Interior.ColorIndex = 45
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Selection.FormatConditions(3).Interior.ColorIndex = 3
End With
End Sub

A:Excel macro w/ IsNumber() and conditional formatting

Read other 6 answers


Posting here before, I received several prompt replies. Due to my success, I will try once again, but this time with a different issue/question.

I have a spreadsheet with 1 column and 3000 rows. In each row, there is a 1 cell block with a series of numbers/characters. I need some type of formatting/macro that will extract only certain characters from each row and place them somewhere else. In example, I have the following rows:

7r74$#@$i4 i3o45$#---$#@$#/4432438
4327$##@$#4343u3i33$$( #343432822
4324*& 345435**(&^*&^342434234324

I need to extra the 12, 13, & 14th character of each row, whether it be a letter, number or space, it doesn't matter. Now the 12, 13 and 14th character need to be placed in there own newcolumn to the right of where they were prior. So the results, would look like this:

7r74$#@$i4 i3o45$#---$#@$#/4432438 i3
4327$##@$#4343u3i33$$( #343432822 343
4324*& 345435**(&^*&^342434234324 435

The post here will not let me space it correctly, but to the right of the first entry would be " i3", the second, "343" and the third "435".

So, it seems pretty simple, but I am not knowledgable enough with formulas, macros or vb to be able to figure this out. I also need to be able to adjust the formatting/macro/vb script every couple months as the "character extraction" place could change.

Thank you, hopefully someone is familiar with what I am trying to do and can understand this basic i... Read more

A:Excel, Formatting/Macro Creation for Data

Are you okay with just a formula? Try this if your data is in A1:


12 is the starting position, and 3 is the number of characters to return.

Let us know if you do need it in a macro.


Read other 3 answers

I found how to send an email from an Excel Macro (http://forums.techguy.org/business-applications/1056070-solved-excel-email-outlook-code.html)

Here is the code I'm using:

Sub Email_Test()
' Macro11 Macro
' Keyboard Shortcut: Ctrl+r
Dim eSubject As String
Dim Sendto As String
Dim CCto As String
Dim Body As String
Dim app As Object
Dim Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)
'Populate variables

mSubject = "Excel EMail Test"
Sendto = "[EMAIL="[email protected]"][email protected][/EMAIL]"
CCto = ""
mMessage = "You have received Knowledge Document Feedback for your domain."

With Itm
.Subject = mSubject
.To = Sendto
If Not IsMissing(CCto) Then .CC = CCto
.Body = mMessage
' .Attachments.Add (NewFileName1) ' Must be complete path
.Display ' This property is used when you want
' .send
End With
Set app = Nothing
Set Itm = Nothing
End Sub
Is there a way to format the message section with bullets? How about paragraph breaks?

Name: John Doe

Date: 6/15/2012
Doc #: KM123456
I'm perfictly willing to look at how-to documentation if someone can point me in the right direction.


A:Solved: Formatting Excel Macro Email Message Section

Hi, I haven't really looked myself but instead of bullets I generally use a dash and a tab
Paragraph as such are not possible but you have the linefeed and carriage return

example to add to your macro code before calling the mail proc

Dim mytext as string
mytext = vbnullstring
mytext = "Dear Jon Doe" & vbclrf & vbcrlf
mytext=mytext & "-" & chr(9) & "Date :" & the date variable & vbcrlf
mytext=mytext & "-" & chr(9) & "Doc #:" & the dos variable & vbcrlf
mytext = mytext & "-" & chr(9) & "Etc :" "etcetera"
mytext = mytext & vbcrlf & vbcrlf
mytext = mytext & "Sincerely yours," & vbcrlf & vbcrlf
mytext = mytext & "Jane Doe" & vbcrlf

vbcrlf can also be replaced wij Chr(10) or Chr(13)

See it this helps.

Maybe html formattingis possible but this works just as fine.

Read other 3 answers


I am trying to create a button for inputting rows of data into a table.

I need the table to input a set amount of rows on the next available space in the table, and populate 1 of the columns with data given in a list.

I know I need a macro of some sort but am not sure how to go about creating it.

I have attached an example spreadsheet for reference.

Any help would be greatly appreciated!

Read other answers


For some reason, the numbers for the left axis are in the middle of my chart and I have highlighted the axis and reformatted to no avail.

How can I change the settings so it is shown on the far left as usual?

I've uploaded a jpg showing the problem.

A big thanks in advance,

/ GE

A:Excel 2010 Chart Left Axis is in middle of chart

Thats the normal default behaviour since you have both positive and negative values on the X-axis - the chart assumes you want to see the value extremes so it places the y-axis at the zero position of the x-axis.

You can shift the Y-axis to the far left by using the format x-axis option, like this:

Read other 3 answers

Hi there,

may somebdoy please advise (I am pure newbie at Macros):
*I want to create button (I can dot that) and assign Macro to it:
*Copy selected cells (C3:N3)
*Paste values against respective product code (product code can be changed manually in A2)

It happens in the same working sheet (or not a problem if haapens in another sheet)

Many thanks in advance (this would save me some time and efforet)


A:Solved: Copy selected cells to chosen cells in Excel with Macro

Read other 14 answers

I'm attempting to write my first macro for an Excel 2003 workbook. I'm not completely code illiterate (I've got moderate skills with AutoLISP), but I'm new to VBA and am not yet an Excel power user, so please be gentle.

The macro I want to write will:
check that the selected cell's content is underlined before proceeding
copy the content of the currently selected cell into an external plain text .log file
.log file lines should be: year/month/day - time - username - cell contents
.log file names will probably need to be generated
clear the cell's content and formatting (particularly underline and text/background color)
Here's what I have so far:
Sub Unpost()
If Selection.Font.Underline = True
Then Selection.ClearFormats And Selection.Clearcontents
If MsgBox("The selected cell is not underlined...are you sure?", vbOkCancel) = vbOk
Then Selection.ClearFormats And Selection.Clearcontents
Else Exit Sub
End If
End If
End Sub
If I've written it correctly, it should currently do everything except log the cell contents. This, from what I've seen, is going to be the trickier part. I intend to use this macro 50+ times per weekday, so at some point the .log files will get too long to be useful, so I assume it will need to automatically create new logs (perhaps "year-month.log"). I've seen some useful info about appending to an external log here and here, ... Read more

A:Excel 2003 macro: log contents of selected cell, clear cell

You need to use the "File Scripting Object" to create and/or append text to a file. I've included a link below to get you started. If you are unable to figure it out on your own let me know and I'll write the code for you.



Read other 1 answers

I have a simple pivot table which uses a calc field to work out a %

so in the data we have,

Branch, region, Cost, Waste, Month

then I use a pivot calculated field to calculate the % Waste/cost for each month using Waste/Cost field (this is to get a correct total for yeartodate - using pivot grandtotal field)
works great

I want just to plot a graph showing last 3 month period for each branch

Now if i select the 3months it calculates the grandtotal correctly for eachbranch - but in order to pivot graph, I have to hide the months, when I hide the months the chart is the correct format however, the values calculated are no longer the selected months, but all the months

how can I chart from the pivot table,( because i want to sort branch in region which works OK in pivot, and chart) with just the grand total shown but from the selected months ticked.

any help please

A:Pivit Chart using just grandtotal from selected fields

Read other 8 answers

Whenever I attempt to update or correct mp3 details in Windows Explorer I ALWAYS get this message. This problem is as old as Windows 7 and still no valid explanation or solution. BTW, I refuse to install any type of Apple software ESPECIALLY iTunes as it
rewrites the registry entries for ALL optical drives rendering them unusable to the point they are even removed from Windows Explorer! I am sick and tired of this constant bull coming from Apple and Microsoft. WinXP had no problems whatsoever in this area
so I guess that explains why the supposedly college educated programmers at Microsoft broke it. They seem to excel in that area with ALL of their software and misclassified updates like the barrage of Get WinX crap they constantly claimed was "Important"
when it was anything but. The problem doesn't exist in Win 8, 8.1 or 10 so why in Win 7?

System Info:
Windows 7 Ultimate 64-bit SP1 (6.1.7601)
AMD FX-8370 Eight core CPU 4.0 GHz

Redmond... We have a problem.

Read other answers

I'll try to explain this best I can. I am working with a lot of excel line charts. Once I create a chart I want them always to get reformatted in the same way. I have the macro written that reformats the charts the way I want them. The problem is I don't know how to write the macro so that it always formats the chart I have selected.

I can have it work for a specific chart (e.g. "Chart 1") by using the following code
ActiveSheet.ChartObjects("Chart 1").Activate

What I would like to be able to do is simply highlight any chart that I have already created and run the macro and have that chart be formatted by the macro

So if anyone can give me code that would make the macro work for any specefic chart I select please let me know.


A:macro to format charts

First you need to identify the chart(s) that are selected then loop through them and do the formatting things that you want do to each of them.

SO, working backwords, you need to declare a "chart object" and write a subroutine that does your formatting things to that chart object:

Public Sub FormatThisChart(ThisChartObj As ChartObject)
-do what you like here to reformat ThisChartObject
End Sub

But that's the back end. FIrst, you need to look at what's in your seletion and decide if it's one or more charts (or something else that you can't work with)
Now you can fool around with this trying test macros to see what turns up in the selection when several things are formatted using the "TypeName(oject)" function.

This function returns the name of the type of the object given in the argument so you can write a macro that simply prints the type of object the selection turns out to be when select different things

Debug.print TypeName(Selection)

I've fooled around a little and found that if you just click on one chart, you get a "ChartArea" object returned and you need to get the .Parent of its .Parent to get to the chart object.

If you select multiple charts, you get a DrawingObjects collection returned whose constituents are CharObjects.

Under some circumstances, you can might get the single ChartObject in the selection.

SO once you've found the selection typename...

If the TypeName is ChartArea, call the ... Read more

Read other 1 answers

In a word document when there are subscripts or superscripts I want to FIND font that is superscript, font color automatic and REPLACE it with font color automatic, not superscript / subscript but raise the font by 3 points. I went through and recorded the steps in Microsoft word but everytime I run the code I get an error. Can somebody please help with my coding:

Sub super()
' super Macro
Selection.HomeKey Unit:=wdStory
With Selection.Find.Font
.Color = wdColorAutomatic
.superscript = True
.subscript = False
End With
With Selection.Find.Replacement.Font
.Color = wdColorAutomatic
.superscript = False
.subscript = False
.Position = 3
End With
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

A:Error for creating a macro to change font formatting

Read other 15 answers

Hi guys I have this code which helps in exporting ppt from excel to powerpoint.

What Macro Does:
Exports Charts from excel work book to PPT

What I want it to Do:
can you please help me in editing the macro so it copies charts from worksheet.so what I mean it has to be edited in suc a way that it takes only charts/ Charts from active sheet and not whole file.

Final Macro

Sub PushChartsToPPT()
'Set reference to 'Microsoft PowerPoint 12.0 Object Library'
'in the VBE via Tools > References...
Dim ppt As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSld As PowerPoint.Slide
Dim pptCL As PowerPoint.CustomLayout
Dim pptShp As PowerPoint.Shape

Dim cht As Chart
Dim ws As Worksheet
Dim i As Long

'Get the PowerPoint Application object:
Set ppt = CreateObject("PowerPoint.Application")
ppt.Visible = msoTrue
Set pptPres = ppt.Presentations.Add

'Get a Custom Layout:
For Each pptCL In pptPres.SlideMaster.CustomLayouts
If pptCL.Name = "Title and Content" Then Exit For
Next pptCL

'Copy ALL charts in Chart Sheets:
For Each cht In ActiveWorkbook.Charts
Set pptSld = pptPres.Slides.AddSlide(pptPres.Slides.Count + 1, pptCL)

For Each pptShp In pptSld.Shapes.Placeholders
If pptShp.PlaceholderFormat.Type = ppPlaceholderObject Then Exit For
Next pptShp
If pptShp Is Nothing Then Stop

Next cht

'Copy ALL charts ... Read more

A:need Help in editing the Macro to export Charts to PPt

Just remove the fro all loops and refer to the active worksheet

Read other 1 answers

Hello, I was hoping for some help with a problem of mine. It's possibly very simple, but nonetheless I cannot figure it out.

Anyway, I have various blocks of data, which are fairly simple. X values in the left most column and then 4 columns of Y values. However, I have many of these blocks in different sheets and the corresponding values are not necessarily in the same rows or columns every time. Is it possible to write a macro that will select the data and create a chart based on the the position of data relative to the box I've clicked on?

Thus far, I have only been able to create macros that work on a specific column/row. For instance, if the X-values are from B5 to B10 and the Y-values are contained within C5-C10 through F5-F10, I can only repeat the macro on these same rows & columns on a different sheet. What I would like to do is perform the identical operation (relatively) on a box of values like this that began on K5, or wherever.

Hopefully that makes sense. Any help at all would be well appreciated! Many thanks!


A:Chart Macro Help

Please post a sample workbook. From what I gather so far you basically want a macro to create a chart using a variable number of rows from the column of your chosing?


Read other 3 answers

I'm new here and definitely and have used Excel many times before, but am still learning. Here's what I'm looking for:

Our delivery service is sent out multiple times per day to different areas

LOCATION UNIT NAME Onsite Delivery Complete
Area 1 Unit 1 9/01/2010 00:10 9/01/2010 00:58
Area 3 Unit 3 9/01/2010 02:18 9/01/2010 03:02
Area 2 Unit 4 9/01/2010 02:40 9/01/2010 03:18
Area 2 Unit 1 9/01/2010 07:04 9/01/2010 07:58
Area 3 Unit 2 9/01/2010 07:10 9/01/2010 08:03
Area 3 Unit 1 9/01/2010 13:18 9/01/2010 14:15
Area 4 Unit 3 9/01/2010 17:05 9/01/2010 18:50

The data is sorted by time left, on site and complete.

We need to show graphically in a chart when each unit is committed to deliver in an area and how long they are on site. We would like to use a horizontal bar chart, where the entire length of a row on the chart would represent a 24 hour period. For each Unit name, a different colored block/section per area representing when they were on site and blank when they were not actively delivering in an area.

From the information above, Unit 1 would have 3 different colored blocks of time on the same bar, showing they were committed from 00:10 to 00:58, 07:04 to 07:58 and again from 13:18 to 14:15 and each Unit would have its own bar. In the above example, the chart would have a total of 4 bars, sorted by Unit and showing blocks for the different areas of delivery. In addition, we would like the areas to be color coded the same on each horizontal bar. For exam... Read more

Read other answers

I have attached an image of what I'm trying to replicate in excel and a workbook of what I have done in excel so far.

I'm upgrading the inventory list from paper to excel, but I'm having some difficulties accomplishing that in excel to replicate the original inventory layout that has been done for decades. I want it to be as user friendly as possible so users don't have to do any formatting or recoding/debugging.

I'm having a few issues, but first and foremost, I thought graphing the inventory as a gantt chart might be easiest to achieve the same layout as shown in the image. But, I'm not sure how to input all the data as it is a different format from your typical gantt chart.

1. Please let me know how I can input all the information on a graph to replicate the graph layout shown in the image.
I inputted a spin button for users to control inventory through the years as I'm planning to use this from now on. However, I'm not sure how I can use the spin button to control the worksheet depending on the year, or if that's even possible. I want users to be able to use the spin button to see which inventory was used during that year. Right now the spin button year reads 2014, when I hit the arrow to go to 2015, I want it to clear all the 2014 inventory and only display 2015 inventory.

2. Please let me know if it is possible for the spin button to control inventory input for each year.
I know basic coding for VBA, but I typic... Read more

A:Modified Gantt Chart with conditional formatting

Hi Antonio,
Welcome to the forum, I found attached file and I think this would work for you, you'll have to work it out but the gantt sheet would be what you need.

There is another one but I'll have to see if I can find it.
I'll do that later.
Whic version of Excel? 2007, 2010 or 2013?

Read other 3 answers

I created a macro for one of my co-wokers that:

Refreshes data using MS Query to pull data from an Access database.
It then formats fonts and headers and stuff correctly on that worksheet.
It then creates a pivot table on a new sheet (using the wizard) based on this refreshed info (it's actually based on a cell range 8 columns wide and 10,000 rows long excluding the header, just in case we get a huge number of claims on one ship).
It then formats the pivot table correctly and renames the worksheet.
Finally, it moves the worksheet to where it should be (still in same workbook).

This worked the first time, and still works now, except for the last part, where it moves the sheet. I now receive an error:

Run-time error '1004':
Application-defined or object-defined error.

I hit Debug to see where the problem was (I know very little VB) and this is what was there. The Bold part is what was highlighted in yellow in the VB window:

Sub Refresh_Pivot()
' Refresh_Pivot Macro
' Macro recorded 04/09/01 by Liz Clark
Selection.QueryTable.Refresh BackgroundQuery:=False
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 11
With Selection.Font
.Name = "Times New Roman"
.Size... Read more

A:Excel 97 macro error

Read other 6 answers

I can run this macro on my own machine but when I transfer it to another computer, I get the following error: Run Time error 1004 Cannot open PivotTable source file 'headcount by area' - any ideas what's wrong??!

A:Error running a macro in Excel

Read other 6 answers

=IF(ISERROR(100),"NA",100) appearing in Excel 2007 shortcut key macro?

First, my disclaimer is that I am not familiar with Excel or VB!​

That being said, one of my users is getting an error when running a macro in Excel 2007 from his personal.xlsm.​
The macro is a simple color change which we have re-recorded several times but when he attempts to run the macro with a shortcut key the field populates with =IF(ISERROR(100),"NA",100) where 100 was the test value of the original cell. If you run the macro from the menu it works correctly. ​
Macro is:​
Sub Macro1()
' Macro1 Macro
' Keyboard Shortcut: Ctrl+Shift+E
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
End Sub​
Any suggestions are greatly appreciated. ​


Read other answers

Hi there,
I've cobbled below code together (I'm game but probably not even a pedestrian in VBA, maybe more of a snail) to take some information from an Excel sheet and put it into an e-mail. I also want the active workbook attached to the e-mail and if possible another file with the same name but in pdf (haven't looked at that yet).

The ErrorCatch says it's an object or applications driven error.

I'm not sure about this line: .Attachments.Add ActiveWorkbook.FullName but really have no clue where the problem is.

Any help would be greatly appreciated. Thanks.


Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
End Function

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String

On Error GoTo Errorcatch
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi Leon" & vbNewLine & vbNewLine & _
"" & vbNewLine & _
"Attached invoice for " & Cells(C14) & ".Can you please approve for payment?" & vbNewLine & _
"Supplier: " & Cells(E43) & vbNewLine & _
"Invoice Number:&quo... Read more

A:Solved: Error 400 in Excel Macro

Read other 14 answers

I have a spreadsheet with macros that I use for work which works flawlessly on Win XP, but when you try to run the macros on a Vista/7 machine it gives this error:

"Compile error: Can't find project or library"

It seems to be hanging up at "Trim" on this line:

NewSheet = Trim(InputBox(Mesg, Title))

Our tech support seems lost, any help?


Read other answers

This post is related to an earlier one, which has now been solved and closed:http://forums.techguy.org/business-applications/955009-excel-2007-lookup-formula.html. The macro outlined in this thread works fine as long as there are 4 values in each row the macro references. If there are less than 4 values the formula errors when trying to return the

value. Could somebody please edit the macro so that if it causes an error due to too few values, that it will then break out of the code instead of keep running? The ideal situation would be that it outputs any values it finds, and skips past and carries on if the function errors.

A:Solved: Excel Macro Error

No problem - all sorted now A bit of out of the box thinking was all that was needed!

Read other 2 answers

Hi there,

I'm trying to do a simple macro that opens an Excel template. Copies fields from a row in an open Excel sheet to the newly opened template. Prints the template. Puts a date into that row. Stop.

I got an error at r = ActiveCell.Row until I deleted Option Explicit. Also got an error at the copies code that I turned into text. Now I get a Compile Error: Named argument not found at IgnorePrintAreas:=False

I tried to delete that line but then I get an error for the entire print code.

Does anybody know where the error sits? I'm trying to print to the default printer.

Thanks, Christine

Sub ProcessServiceInvoice()
Dim BkSrc As Worksheet, BkDest As Worksheet
Dim FilePath As String, FileName As String
Dim blnOpened As Boolean
Dim DestBook As Workbook
'Set Source Workbook
Set BkSrc = Workbooks("D:\Documents and Settings\305015724\My Documents\admin\Accounts\2010\_Service_Invoices_2010.xls")

r = ActiveCell.Row

'Open Template
Workbooks.Open FileName:="D:\Documents and Settings\305015724\My Documents\Templates\AP coding template_General_NZL.xlt"
Set BkDest = ActiveWorkbook
'ActiveWorkbook.SaveAs FileName:=BkSrc.Cells(r, 3).Value
'Copied from Zack Baresse, I think I will need this
'Call ToggleEvents(False)
'Set ws = ThisWorkbook.Sheets("CodingTemplate")
'If WbOpen(BkDest) = True Then
'Set wkb = Workbooks(FileName)
'blnOpened = False
'If Right(FilePath, 1) <> App... Read more

A:Compile Error in Excel VBA macro

I have worked a bit more on this and above problems seem to be resolved. But, big but, I now get a run-time error '9'. Subscript out of range.

I looked it up and it says it means one of the workbooks isn't open but I'm working with 2 books, one which I have open to start with and the other one I do open.

Any ideas? Thanks, Christine

Sub ToggleEvents(blnState As Boolean)
'Originally written by firefytr
With Application
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState Then .CutCopyMode = False
If blnState Then .StatusBar = False
End With
End Sub

Function WbOpen(wbName As String) As Boolean
'Originally found written by Jake Marx
On Error Resume Next
WbOpen = Len(Workbooks(wbName).Name)
End Function
Sub ProcessServiceInvoice()
Dim BkSrc As Worksheet, BkDest As Worksheet
Dim FilePath As String, FileName As String
Dim blnOpened As Boolean
Dim DestBook As Workbook
'Set Source Workbook
Set BkSrc = Workbooks("D:\Documents and Settings\305015724\My Documents\admin\Accounts\2010\_Service_Invoices_2010.xls")

r = ActiveCell.Row

'Open Template
Workbooks.Open FileName:="D:\Documents and Settings\305015724\My Documents\Templates\AP coding template_General_NZL.xlt"
Set BkDest = ActiveWorkbook
'Copied from Zack Baresse, I think I will need this
Call ToggleEvents(False)
Set ws = ThisWorkbook.Sheets("CodingTemplate")
... Read more

Read other 1 answers

I have an Excel chart stuck in potrait mode although it is setup as landscape in page setup. Any suggestions on how to get it into landscape?

A:Excel charts

When in page setup go to Options.. and see if that setting has portrait selected.

Read other 2 answers

Using a line chart I only have two days worth of info. On day three the number is zero causing my line to point to say 20, then 30 then DROP to the bottom. Is there a way to have the line only show for items with information and not show for zeros?

I was going to attach the worksheet but I don't know how to convert it to the required format. Help on that too?

A:Excel Charts

Not without removing the zero. I even tried an IF statement for you, but as long as there's something in the cell, it sees it as a value. If the cell is blank, however, that'll work.

There may be some VBA thing that'll so it.

Read other 2 answers

hey ppl....i have this data i have posted the excel sheet there...
i have a problem... u see those two columns onw which says weight of clay and the other that says permeability??...

i want the values of "weight of clay" on the x-axis and the values of "permeability on the y-axis but i have tried for over 2 hours and i cant figure out how..
how i wish i had office XP right now

pls help i need to submit it tomorrow
thanx in advance

A:Need help with excel charts...

Hmm...hopefully this isn't too late, but I only just stumbled across this. I'm going to presume a "Scatter Plot" since you didn't specify. Process is basically the same for any of them; adjust accordingly.

1) Don't highlight your columns.
2) Click the "Insert" tab at the top.
3) Click "Scatter" from the top panel, then select one of those options (I usually start with the one with no lines connecting anything).
4) This automatically creates an empty graph on your screen and changes your active tab to "Design".
5) With the graph selected, click "Select Data" from the top panel.
6) In the pop-up here, click "Add" under series.
7) Type in a series name (Do not hit "Enter")
8) Click on the icon next to "Select Range" for "Series X values." Select what you want on the X-Axis.
9) Repeat 8 for y-values.
10) Use the "Chart Layouts" tabs on the top to find the chart closest to the format you want. Inside the chart, you can make any adjustments you want (e.g. change titles, delete legend, format axes, etc).
11) If you want to move the chart to its own page (I find this is often helpful), click "Move Chart" from the far right of the Design tab. Place it where ever you like.

I hope this helps.

Read other 2 answers

I need to be able to add comments to points in a line chart. They need to
work just like comments where they dissapear if they're not clicked and they
appear when clicked. Anyone know how to do this?

A:Excel Charts

Read other 6 answers

I have a pie chart which shows one series eg series 1, I have added in another series eg series 2, does anyone know how I can keep one pie chart but switch between the series where required......

A:Pie Charts in Excel XP

Hi! Use a vlookup and data validation as demonstrated in the sample file:

So, make a dropdown to choose a series.
Then, make your series values do vlookups to get your data from another worksheet.
When you change your dropdown, your series changes.
But your chart is based on the same cells.
Kinda cool. Kinda dynamic without using code.
As long as you have the same cells, you could, for instance, flip through expenses for each month of the year this way.

Here's the Data Validation steps:

Here's the Vlookup steps:

Hope it helps!

Read other 1 answers

I am a new Excel user and I'm making a spreadsheet at work that has employee names in one column and information about them in the others. I am trying to make a chart that will show information for each category. For instance, if one column was eye color, I would have things like blue, brown, hazel, etc. in the respective fields. How would I make a chart that shows what percentage has brown, what percentage has blue, etc.? I can't get it to work unless numeric values are entered in the fields. ...Thanks!

A:Excel Charts

You can use this formula:

Where [range] is the range of cells to count each instance of each color replacing "Blue" with the color or text string to count.

Format the cell as percentage.

Read other 2 answers

Is there an easier/better way to make a chart that may make more sense ???

A:Excel Charts

i would probably do something like a comparison bar graph for the oil
and a line chart for the temperature

like the attached

but it depends on what you are trying to show in the graph

if you had all the months oil usage - you could see if the oil increased when the temp decreased and so have some correlation between the temp and the oil consumptions used

Read other 3 answers

In 95 and 97, when I create a chart of 12 items or so, the chart only displays with half of the bars showing unless I really stretch the page out. Anyway to make it all fit on a single printable page?

A:Excel Charts

Read other 8 answers