I have an excel based report consisting of several worksheets. Each worksheet contains VBA code which allows the user to generate custom reports with a series of Excel Charts being generated. I have two related problems with the output/display.
Whenever I save the file the code is triggered causing each sheet to update and scrambling charts. (To fix this I have to leave the report and then re-enter, which then works fine).
Is there anything I can do to stop this from happening? For example, to avoid the code being run on save or to refresh the active screen. A couple of things I should mention: I already have screenupdating turned off so the user is not seeing each action being calculated. At present to avoid the chart display glitches with complex tasks, I have added a line to my code at the end which moves off screen and then returns to the current screen. That ensures the chart no longer lingers on screen, however it does mean that for a split second a phantom graph appears in the middle of the screen before disapearing.
Within the code I am asking excel to sort two difference worksheets with virtually the same code, except that one of the worksheets does not get sorted. If I step through the code, it works and the worksheet is sorted, but if I just run the macro the worksheet does not get sorted, as if the line of code in question is being skipped. Pointedly, my question is - Why does the code work while debugging (stepping through) but not when I generally run it?
Also, as far as the code is concerned, I am sure that the all variables contain the information I need them to and am also sure that any ranges created from those variables are also the ranges they should be. Here are the two lines of code that are supposed to sort. The UBOC cash sheet gets sorted, but the LNB cash sheet does not.
I was playing aroung some VBA code, but the code executes 98 times and I don't know why. For example, range A1 contains 5. When I type, say 3, in A1, I want A1 to show me 8 (5+3). Then I may type 10, in this case A1 must show 18 (8+10). And so on. I decided to place entered values in different column, then sum them in A1 with below stated code
I have created code to enter data from the Active cell. However, I would like to edit it so that the user can only make this work if they are on Rows 6 through Row 29.
Here is the Sub DropDown7_Change() 'Right here I want: If ActiveCell. is in Row 6-29 Then Application.ActiveCell.Value = Range("D3").Value Application.ActiveCell.Offset(, 1).Value = Range("F3").Value Else Msg = "Move into the proper rows" End Sub How can I do a check before the code is executed to be sure the users active cell is in Rows 6-29.
it would be possible to have text disappear after being displayed in Excel for a while.
My son and I played around with games (helping create a photographic memory, I told the youngster!) on the thing and until yesterday neither of us noticed that clicking the mouse anywhere (actually just clicking it) causes the screen to "freeze" while the code still happily erases the text in the background.
Hitting ESC allows the screen to cath up to the actual code EVEN THOUGH the ESC key is actually disabled from breaking the code.
I have the following code attached to a userform. It works perfectly (for what I need) although I know it may not be perfectly written. However it does seem to take for ever to execute considering it's just filling some cells on different worksheets. Have I missed something out or got it tied up doing something that takes a long time. If it's just a fact of life that it takes this time then I can live with it.
Private Sub UserForm_Activate() Dim wsCB As Worksheet, wsLL As Worksheet, wsBond As Worksheet Dim rngFound As Range Set wsCB = Sheets("Current Bonds") Set wsLL = Sheets("Landlords") Set wsBond = Sheets("Bond") TenancyStartTxt = Format(TenancyStartTxt, "dd-mmm-yy") RnwlDteTx = Format(RnwlDteTx, "dd-mmm-yy") Application. ScreenUpdating = False 10 MyBond = Application.InputBox("Bond number for renewal?", "Bond Number") strFindMe = MyBond With wsCB Set rngData = .Range("c13:c490").......................
I have a workbook with a chart on a worksheet called Area Report
There are 10 areas in total A - J, to view a particular area cell I2 is validated to only accept entries A - J which represent the Areas, selecting a particular area updates the chart for that area
Is it possible to have a macro that will print all 10 Area Reports, I tried to do it using the Record Macro function, the code was as follows .......
I don't know if this is possible, but is there a way to have a macro in Excel copy and paste charts into a Powerpoint file? I've got a program that creates all the material needed for a Powerpoint slide deck and I want to automate the copy/paste work.
For Each wShape In ActiveSheet.Shapes wShape.Delete Next wShape
This works great to delete the buttons and rectangles I have on my worksheet, but it is also deleting charts. Is there any way to avoid the chart deletion?
I need to create 63 charts from data which I have in two columns. I want to create multiple charts using one macro. For the first chart I want it to use cells K2:K80 as the x values, and M2:M80 as the y values. For the next chart I want it to use cells K81:K159 as the x values and M81:159 as the y values. For the next chart I want it to use cells K160:K238 as the x values and M160:M238 as the y values. I want to continue this, creating a chart for every 78 cells of data, all the way until the 63rd chart which uses K4900:K4978 as the x values and M4900:M4978 as the y values. I have created the following macro by " recording." This macro generates the first chart that I want:
Sub Macro5() ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$K$2:$K$80" ActiveChart.SeriesCollection(1).Values = "=Sheet1!$M$2:$M$80" ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveWindow.SmallScroll Down:=-3 End Sub
How can I alter this macro to create all 63 charts?. It seems like there is an easy way to do this, but I don't use macros very much (at all).
Consider two instances of Excel - one with a workbook containing a chart and some VBA code, the other containing a blank workbook. When I paste a chart from the first instance of Excel to the other, all the VBA code from the original workbook is also pasted into a new third hidden workbook in the second instance of Excel! If that weren't weird enough ON WINDOWS XP (but not Windows 2000) if the VBA code included WorkbookOpen or AutoOpen this code is executed in the second instance of Excel, which of course fails if it references sheets that only existed in the workbook open in the first instance of Excel! This is the first time I've seen the same version of Excel do different things on different versions of Windows! Example attached - What's going on!? Auto Merged Post Until 24 Hrs Passes;...note: this does not happen when pasting charts between workbooks open in the SAME instance of Excel
I have the code below that puts in the file name, date and time and page numbers. That works fine, however, I changed the name of this macro to AutoOpen thinking that this would execute upon opening an Excel doc -- I think this works for Word, that's why I tried it. Anyway, it doesn't execute upon opening an Excel doc. Can anyone tell me how to get that going? Also, the font of the file name comes at at 12 point while the other entries come in at 6 pt - any way of making these all consistent, say 8 pt? Thanks for your help.
By the way, I'm a novice at Excel macros so being fundamental in your answer would be very helpful (i.e., don't know why there is a distinction between Regular Modules, Worksheet Modules and Workbook Modules. Seems to me I should always be creating Regular Module macaros so that they are accessible to all Excel docs but, again, I'm new at this so I'll have to read more about these items as well).
i have an excel file with some vba code that includes some functions of a third party add-in also. It works fine when i open this file on the host machine. (Assuming host machine is where the Excel File is placed) But when i try to open the same file from some other computer(remotely/by sharing folder), the add-in malfunctions because of one reason that it considers the remote machine to be its host. Is it possible to set a permanent host machine for that any add-in. Or is it possible for that add-in / Excel file to do its calculations and updation of links on the host machine where it is placed.
I have an excel workbook created in 2007, but now running in 2010. In the workbook I have a form Combo box with a macro assigned to it. When I click any value in the combo dropdown list the macro executes. In 2007 I could click the combo dropdown box and then press ESC to exit from the form control without executing the assigned macro. Similarly I could also click on any other cell to exit from the control without executing the macro.
But, in 2010 if I press ESC or click on any other cell the macro will execute. Sometimes I want to be able to exit from the combo box without executing the macro.
When i add a month coloumn,Sparklines and Average coloumn should get updated automaticaly.Now this is not happening even if the data is in table format.I also want the graphs to be automaticaly updated.
Dim strLoan As String Dim longCat As Long Dim rHere As Range Range("A1").Select [Code] .....
I can see the cursor move to all the desired cells when this macro executes; it just isn't dropping any data where it should be. I have been working at this stage for the last five hours with no success. I don't know whether my copy-paste methodology is broken or if it is my selection criteria
I keep getting an automation error in excel 2010 when I click on the button that executes the macro. Excel crashes when the box appears. There is no code in the error box. Why I get this error message.
I am trying to put some charts into a report that is pivot table based. I have some code that will work if the pivot tables stay stagnic but the users may change the tables around so that could be an issue of new data. I have the following code where I changed the source to the pivot table name (general name because of numberous report possibilites). I have taken out the other charts because they are just a variation of the chart 1. I am crashing on the line with the * on it.
Sub UWTierChart() Dim oCell As Range Dim oChart As Chart Set ws_data = ActiveSheet 'Chart 1 Sheets(wsPT).Select Range("B21").Select
I was trying to add a popup box so that if no match was found, it would report a popup box stating so.
Well, It runs successfully, however it executes every, single time! LOL
Sub Find_and_Update()
On Error Goto A
Dim Search_Range As Range, Found_Range As Range Dim SearchFor As Variant, cell As Range
SearchFor = Range("J3").Value Set Search_Range = Range("A:A")
Set Found_Range = Find_Range(SearchFor, Search_Range, xlValues, xlPart, False) For Each cell In Intersect(Found_Range.EntireRow, Columns("G")) cell.Value = cell.Value + 1 Next cell A: MsgBox "Not Found" Range("J3").Select Selection.ClearContents Exit Sub
Range("J3").Select Selection.ClearContents End Sub
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
I've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.
I have never done this before and I am having a hard time - it seems that none of the graphs in the Excel menu accommodate what I want and I have no idea what I am doing to boot.. Could someone PLEASE help?
My data is: American & Japanese workers can each produce 4 cars a year. An American worker can produce 10 tons of grain a yr. / Japanese 5 tons of grain a yr. Each country has 100 million workers.
Need PPF and Opportunity cost of car & grain from U.S and Japan.
I have at this for two days and I cannot get the information in the chart the way it is supposed to be. It makes no sense evry way I have tried it and the graphs don't even look correct. I've tried several line graphs and scatter graphs..
I update graphs/charts everyday. I've been looking at ways of updating a lot of charts in 1 action.....
What would the formula be to have the range of the chart (ie =Data!$BS$1188:$BS$1201) to get the row numbers, not the row, from numbers entered elsewhere on a data sheet (ie =Data!$BS$1188:$BS$1201)
As a lot of the time I use the same number range but on different rows and sheets.
I'm trying to find a way to select all charts in a worksheet and pasting them into a new sheet as a jpeg and can't find a way to select the charts all together as a group like if you shift click them.
I'm looking to copy and paste them as a group because they are grouped in a specific way for a report.
I have 2 charts on one sheet. Both the charts are generated using 2 different VBA code. I have also created a Button to trigger the code and generate chart when the button is clicked.
I recently realized that I was creating charts upon charts and was making the excel file bigger in size all the time.
So, I wanted to see if I can delete the individual chart before generating a new one.
Currently, I am using the following code to delete the charts.
I am having lots of issues with generating a scatter chart in vba. I tried recording a macro but the only thing that recorded was my different selection clicks on the chart. No formatting was recorded. I have the following ....
