I have written a macro which references to several different sheets and cells therein. The macro is assigned to a command button on 'Sheet 1'. When I click the command button, the screen flickers and the user can see the macro running all the commands I have written. Is there a way of stopping this - maybe replacing it with a static screen view whilst the macro runs?
View 3 RepliesSo i don't have any events that i know of that should trigger my macros to run. But everytime i delete a cell, or hide rows, it seems all my code/macros just auto run. And i had a few times when i hid the rows, the code would start, and then my excel application would crash.
View 4 Replies View RelatedOkay, I have this code and it works very well - but it only works if my WORKBOOK is unprotected. I know how to Unprotect then Protect an ActiveSheet, but I can't figure out how to Unprotect then Protect my workbook when the macro runs..
Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In ActiveWindow.SelectedSheets
[Code] ......
I am trying to use following codes for a add-in utility so that user can enable or disable logging activity for a given workbook.
My problem is to how to add the following code to a new workbook so that "Loginfo" function could be activated. I have tried some application... based commands but it do not seem to be working.
I have made an class and created a file to log when user change value or insert formula in a sheet get recorded in a file C:Logme.csv. The problem is it is recording the value and formula at random i.e in some instances it records the value and in some instances it is not recording the value and formula in logme.csv . I am unable to figure out why this is happening?
View 3 Replies View RelatedI am dealing with several very large spreadsheets using VBA to do various things. I found that my code worked well, but was taking a long time to run. The biggest time consumer was my use of the AutoFilter features. I have since turned calculations to manual before my code runs and set it back to auto when my code is done running. What are the potential consequences of my turning calculations to manual and then back to auto?
View 2 Replies View RelatedI need to insert code into an existing macro that does each of these two similar actions:
If a specified word is in the heading (row 1 cell) of a sheet, DELETE that column
If a specified word is in the heading (row 1 cell) of a sheet, BOLD that column.
I have a series of macros altering various workbooks and sheets. They're numbered Step1, Step2, etc. In my Step5 I have a series of Case statements in a macro, and I don't know how to get it to do what I want next. I have NOT tried running this code yet, and I'm sure I have something(s) wrong in it.
1) If the selection in DstWbk, sheet "Steps", is "01DSP" through "11DSP" the macro needs to delete specific columns in the SrcWbk, and then move to the next step (6).
2) If the selection in DstWbk, sheet "Steps", is "*DSP" the macro needs to go directly to the next step (6)
3) If the selection in DstWbk, sheet "Steps", is anything else the macro needs to flash a generic "No Data found" message and move on to Step7.
The part of the code that's throwing me begins at 'Select only the specific regional data' and ends at the "Case Else MsgBox"
I'm at my wits end trying to work out why I'm getting a circular reference when this code runs:
Private Sub TextBox1_Change()
Cells(Rows.Count, "K").End(xlUp).Offset(1).Value = Range(TextBox1.LinkedCell).Value
With Range("K5").Resize(40, 1)
.FormulaR1C1 = "=OFFSET(" & Cells(Rows.Count, "K").End(xlUp).Address(True, True, xlR1C1) & ",-(ROW(RC)-ROW(R5C)+1),0,1,1)"
.Value = .Value
End With
End Sub
The circular reference cell is K29. I have an "X" in K46 after which all the values in TextBox1 are copied.
I have some code to put a GIF animation into a web browser on a userform. This works fine - on its own. However, once the GIF has loaded I want to run a whole bunch of code in the background. If I run the code all at once, the userform appears but the picture doest show - but when I place a breakpoint between loading the userform and the main sub routine of my code - the GIF loads and the animation is shown all the time my code is running in the backgorund.
View 4 Replies View RelatedI have some code sat in worksheet_change (or worksheet_pivotupdate):
If ActiveSheet.PivotTables("PivotTable2").PivotFields("Area").CurrentPage = "(All)" Then
ActiveSheet.PivotTables("PivotTable3").PivotFields("Area").CurrentPage = "(All)"
End If
If ActiveSheet.PivotTables("PivotTable2").PivotFields("Area").CurrentPage = "London & Essex" Then
ActiveSheet.PivotTables("PivotTable3").PivotFields("Area").CurrentPage = "London & Essex"
End If
The problem is that this code runs over and over again, as each time the pivot table updates, it constitutes another update, and so on and so on....
I am making a small push button calculator to enter data into a textbox on a userform in an add-in file and this works fairly slowly. I am trying to concatenate a list of numbers in a textbox that simulates a calculator screen when entering numbers.
Private Sub CommandButton6_Click()
Dim Val
Val = "6"
Dim valand As Range
Set valand = ThisWorkbook.Worksheets("Stageing").Range("K65536").End(xlUp).Offset(1, 0)
valand = Val
Dim A As Range, B, C, d, E, F, G, H, I, J
Set A = ThisWorkbook.Worksheets("Stageing").Range("K2")
Set B = A.Offset(1, 0)
Set C = B.Offset(1, 0)
Set d = C.Offset(1, 0)
Set E = d.Offset(1, 0)
Set F = E.Offset(1, 0)
Set G = F.Offset(1, 0)
Set H = G.Offset(1, 0)
Set I = H.Offset(1, 0)
Set J = I.Offset(1, 0)
TextBox1.Value = A & B & C & d & E & F & G & H & I
End Sub
I have a spreadsheet that calculates percentages and then outputs the results to a pie chart. There are 9 different percentages being graphed in cells A41 to A49. The chart looks weird if any of the percentages end up being 0, so I have the formula set to add 0.00001 to each calculation (so they show up as 0% and display on the chart as 0%, but truly are 0.00001). I would like it so that if any of these 9 percentages ends up being 0 (or really 0.00001) that the row automatically hides and thus won't display on the pie chart. How can I create a macro that automatically runs to accomplish this, and automatically updates as percentages are recalculated.
View 3 Replies View RelatedSee attached file illustrating exactly what I'm trying to do.
I won't repeat the exact same things I wrote in the file, but the Summary is that I need to first make such that only certain check boxes can checked at any one time, then make the command button run certain macro(s) depending on which boxes are checked.
I have a long complicated macro that processes a ton of data and gives the output in a new sheet. I then want to create new buttons (or some sort of user clickable triggers) on this new sheet that runs another macro.
I have got the actual adding of buttons in the sheet working, but I can't find a way to automatically assign macros to newly generated buttons during runtime. Is this even possible (or are there any clever tricks I could use to get around this?)
Essentially, I am working with a database for a school and up until this point, each row matches up to a student with a bunch of information on them. One of these pieces of info is their parent's names. This is a much simplified look at things, but the screenshot below is a basic version of what everything looks like:
Now, the school wants me to split up the parents names from the student's and place them in their own row. I have recorded a macro to do this where you make the active cell the "full name" column for the row you want to expand, and then run the macro. Afterwards, up to two more rows are added, with the parent's names and some data copied. Below is a screenshot of what that looks like: (yellow is the new data generated, and blue is the active cell I marked to run the macro properly)
The issue is that there are thousands of these that need to be done, and I figured there is a way to do this with a macro. The issue is that not every row has parents names to expand out.
In layman's terms, I need a macro that will look at the two columns titled "Father's Name" and "Mother's Name" on each row of the spreadsheet and then, if there is any data in both or either of them, it should set the active cell to the "Full Name" column for that row then run the macro accordingly. After that it should repeat this all the way down, skipping any where neither of the columns have text in them
In the actual spreadsheet, the columns that need to be checked are AW and AX, and the column that needs to be set to the active cell before running the macro is B.
i need to make a macro that takes workbooks or files and
runs them through another macro. i already have the 2nd macro
done and it is working perfectly i just need to know how to make the one
that finds the other files and runs them all through the macro i already made. My boss said that he will have about 150-200 files to run through this macro.
I can't seem to find an answer for this, but as an alternative, on Worksheet Activate the code runs and places a 1 in Range("A1") and therefore the next time the macro will not run.
This works fine for me, but just thinking if somebody who doesn't know why the one is in A1 and deletes it this macro will run again on sheet activate.
Private Sub Worksheet_Activate()
Dim LR As Long
Dim rng As Range
If Sheets("Charts").Range("A1").Value = 1 Then Exit Sub
LR = Sheets("POD").Cells(Rows.Count, "B").End(xlUp).Row
With Sheets("Charts")
.Range("I129").FormulaR1C1 = "=ROWS(R1C1:R[-128]C[-8])"
Set rng = .Range("A129").Resize(, 10)
rng.Copy rng.Resize(LR - 4)...............
Running Windows XP; Excel 2003
I have a fairly simply macro.
It copies certain sheets out of a workbook (for a new workbook)
Unprotect the sheets in the new workbook
Deletes a particular cell on each sheet
Unhides a sheet
Sets all of the new worksheets to normal view
Protects the new sheets
Lets the user save and name the new workbook.
All is working fine Except it will not change the new workbook from Page Break View to Normal view.
The code I'm using = Sheets("name").Activate
Activewindow.View=xlNormal
Here's the strangest part. When I run the macro from a command button, the newly created workbook retains the page break view (as is in the original).
When I run the macro line by line via F8, IT WORKS JUST FINE
I have created a macro in xl2000 which was working fine - although through corporate upgrades my machine has now been upgraded with xl2003 and the macro runs for a bit and then crashes on the code below, although it only highlights teh 'rnum'
rnum = ActiveCell.Row
Is there a difference between xl2000 & xl2003?
The code below runs on a spreadsheet that has approx 600 rows which INDEX and MATCH another spreadsheet which has approx 600 rows. I takes about 4 mins to run.
Sub QC()
Range("T1").Select
ActiveCell.FormulaR1C1 = "Compared CCCD"
Range("V4").Select
Columns("T:T").ColumnWidth = 15.29
Range("S1").Select
Selection.Copy
Range("T1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("T:T").Select...................
I have a macro which reads data from a large number (up to 500) of books in a folder. This extracted information is subject to some fairly heavy analysis which means that the macro runs very slowly as the sheet is constantly calculating.
I would like to add some code - maybe after 'screen updating = false' - that would turn off auto calculation while the macro runs and then restore it once the macro is finished.
i have a button on my workbook1 that opens another workbook (lets call it workbook2) and automatically runs a macro on opening it. Once opened i have a button on workbook2 to close itself. I then go back to see workbook1. This all works but then when i click on the button to open the workbook2 again it opens but the macro does not run.
View 4 Replies View RelatedIs it possible to have a macro which effectively runs all the time.
For example, if the user imputs something which is wrong I want the macro to pick it up straight away with a message box.
I searched for this and found a few examples on splash screens, but if someone could help I would appreciate it.
Basically I have this massive macro that runs, I recorded some of it, and VBA the rest. It does a bunch of things like bringing in different spreadsheets, formatting vlookups blah blah blah... Anyway for a nice touch I was hoping that I could get a splash screen or something that will run while this macro is running in the background. How hard would this be? The macro is button driven...
I have a macro in my worksheet that runs as a change event on certain comment cells. This macro unprotects the worksheet, expands the merged comment cell that just change, and re-protects the worksheet. The cell expansion works great. When I re-protect the worksheet all of the cells work correctly, except the cell that was just changed. Any other "unlocked" cell in the document stays unlocked after the protection is applied, but the cell that just changed becomes locked. I need this cell to remain unlocked to allow the user to update the comments. Here is my full
View 3 Replies View RelatedI have a spreadsheet that is made up of single line items with sub-totals at various points going down the sheet (ie the spreadsheet is split into sections)
Each section is about 100 rows long and 40 columns wide, 10 of which are sub-totals, all using the same formula from that i enter in column A.
As i add new sections i have recorded a simple macro that copies the active cell (ie A15 for example). It then pastes into D15, F15, H15, N15, X15 and then returns to A15.
I have linked this up to ctrl X for ease of running.
I have around 500 section totals to "ctrl X", per spreadsheet and 10 spreadsheets to do in total. I have firstly filtered all the line items (rows) out to leave me just the sub-totals to [ctrl x] [arrow down] [ctrl x] [arrow down] [ctrl x] etc etc etc....
Is there anyway to automate this process, i have tried recording another macro of me [ctrl x] [arrow down] about 30 times, but when i run it, it pastes on the hidden rows, not just on what you see on the screen. Is there a bit of code i can enter so it only sees what is visible.??
I have a peice of code which opens up a number of Workbooks (200+) and updates a series of control tables in each. Given our security etc environment, links and external references etc are not possible. The target files must all be standalone and self-sufficient.
The problem I have is that when I run the procedure in question in the usual way, the procedure simply terminates after the first file is opened. No error messages, no distress - it just stops. I have put debugging calls all around the "Workbook.Open" call, and it simply doesn't make it out of the call. There is no Auto_Open code in the workbooks being opened and (as I indicated above) no external links, references or function calls.
What's weird is that if I step through the procedure in the Command Debugger, everything works fine. Even weider, if I complete step-by-step processing on the first target file then I can press "Play" and the code will complete execution and process all the remaining files without error.
I have rebuilt/recreated/re-ordered the target files and even moved them to a different volume.
If anyone has seen this before, or has any idea what the cause may be, or even just a wild idea, I'd love to hear it.
Code segment attached.
*
'*** Process each file in the list provided
'*
For intCounter = 1 To intWBS_Files
Call Debug_Msg("Opening WBS File")
Application.EnableEvents = False
Set wbkThis_WkBk = Application.Workbooks.Open(strWBS_Files(intCounter), 0, False)
Application.EnableEvents = True
Call Debug_Msg(("Opened WBS File:" & Chr(13) & strWBS_Files(intCounter)))
If (wbkThis_WkBk Is Nothing) Then
Call Write_Error_Record("File Open Failed", strWBS_Files(intCounter))
Else
Code continues.....
I have a button. When pressed, it runs a macro. Instead, after pressing the button, I would like the user to confirm to run the macro and display some info about it. This is to make sure the user does not accidentally run the wrong macro (button). I just need a prompt box, with a line info, and the two choices YES and NO. If NO is selected, nothing happens. If YES is selected, macro continues.
View 6 Replies View RelatedI got a code that makes an animated userform popup (web browser in userform). that works smoothly. but, when I have that code before two more codes. The userform does show, but the animation does not. Until all the following two codes are finished running. The purpose was to have the animation running while the two codes are being processed.
Main Code: Code with 3 different codes.
Private Sub cmdAdd_Click()
Dim iRow As Long
If Sheets("LookupLists"). Range("N2").Value = "EXACT" Then
Application. ScreenUpdating = False
' Loading user form popup
Call ShowFrm
Call CopyEmplInfo
End If
Call MergeCells
End Sub