Return To Activecell Prior To Macro Run
I have a macro that selects various cells whilst it runs - nothing unusual there! What I'd like it to do is return to the activecell immediately prior to running the macro. So if cell 'B34' is active and then the Macro runs it currently returns to cell 'A12' (the last selection in the macro).
I would like it to return to cell, in this instance, 'B34'. The activecell could of course be any reference within the spreadsheet, so whatever is active prior to running macro is returned to when macro is complete.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Return To Active Cell After Macro Run
I am using Excel to tabulate scores for my employees. We work in a very busy and open office, so there is a need to be able to obfuscate the scores, but also help keep from losing my place while punching the scores. I would like it to be able to return to the last cell that I was punching a score in...I used "ActiveCell.SpecialCells(xlLastCell).Select". I have also used " x= cells(Rows.count,2).end(xlUP).row" followed by "cells(x+1,2).select", but both with no luck... Sub Hide_Scores() Range("B15:EU35").Select ActiveSheet. Unprotect With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0
View Replies!
View Related
Return User To Cell After Macro Has Run
I am writing some code where I will need to refresh data and do a few calculations while the user of the spreadsheet is working on the information. How do I write the code for VBA to record what sheet/ cell the user is currently on and then do i use the "GoTO" function to return the use to where he/she was when they left off.
View Replies!
View Related
Macro Is Checking Once The Prior Reference
I have checked the following reference whithin Excel VBA's references Manually: Microsoft Visual Basic for Applications Extensibility 5.3 BUT when I run the following Macro, once checked the prior reference: Sub AddModuleToProject() On Error Resume Next Debug.Print Err ' I GET 0 Dim VBProj As VBIDE.VBProject: Set VBProj = ActiveWorkbook.VBProject Debug.Print Err ' I GET 1004 Dim VBComp As VBIDE.VBComponent: Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule) Debug.Print Err ' I GET 91 VBComp.Name = "NewModule" Debug.Print Err ' I GET 91 End Sub
View Replies!
View Related
Run Macro When Range Value Not Blank, If Not Run Other Macro First
I wanted to modify the below macro but don't know how. Could someone help me please? Please see the bold text below. Dim shortmonth As String Dim todaysdate As String Dim enterdate As String 'GET DATE AND CREATE VARIABLES enterdate = InputBox("Please enter the date in the following format: MMDD") shortmonth = Left(enterdate, 2) todaysdate = Mid(enterdate, 3, 2) Workbooks.Open Filename:= _ "S:CMU Trade VerificationTrade Discrepancy Report " & [shortmonth] & " " & [todaysdate] & ".xls" Sheets("Daily Archive").Select ***Do this step when when range A2:N2 is not blank, otherwise, run the "Daily Data" macro first and then do the below steps.
View Replies!
View Related
Calculating Prior Year Info
My Financial year runs from Oct to Sept. My current month is June 2008 and the YTD Expenses is in Cell P2032. I have set up a formula to calculate the prior YTD Expenses as sum(AU2032:AM2032) This is for the period Oct 2006 to June 2007. When I do July 2008, I would like to the formula to caculuate the prior YTD Exp as sum(AU2032:AN2032) How can I get the formula to Change Automatically based on the Month & Year as per the example shown above?
View Replies!
View Related
Rename Workbook Prior To Saving
I have a need to simply rename a workbook without saving. I have a master template named "IR QT" but, within the workbook, I run a macro that saves the file with content from a cell as the filename. After the 'Save' macro is executed, the sheet stays open, as planned, but obviously the file has been renamed. What I would like to happen at the end of the 'Save' macro is for the sheet to be renamed (not re-saved) to "IR QT". I just need the VBA code to put at the end of the 'Save' macro to do the rename back to "IR QT".
View Replies!
View Related
Record A Cell's Prior Value
Could it be possible to "sequentially offset" a vlaue, say, of A1 such that, for instance, when A1 changes from "5" to "6" (via formula, not direct input), A1=6 and, say, B1=5, and so on? You know, like keeping a value record of A1, one step back, in B1. This would be UNBELIEVABLY valuble to me. Thus, would I be in eternal debt to he/she who would resolve this here.
View Replies!
View Related
Finding The First Nonzero In A Row And Then Deleting All Prior Cells
I'm trying to create a macro that will look in each row and find the first nonzero that it contains, and then proceed to delete all prior cells before it and shift the existing cells in that row to the left. Now I know that the formula to find a non-zero in a row is: =(MATCH(TRUE,INDEX(B9:BE9>0,0),0)-1) and I figured that to select all cells before it you would simply use: Range(Selection, Selection.End(xlToLeft)).Select Selection.Delete Shift:=xlToLeft My problemms are: I can't figure out how to translate that formula into a piece of the macro and also instead of having it simply calculate how many zeros there are until the first nonzero to get it to select that first nonzero How to make the selection conditional upon the row. At first I thought maybe I could just have it paste the formula into the first cell of the row but I realized that it wasn't possible to do as the formula already has the row selected How to have it select all the prior cells to the first nonzero, delete them and shift existing cells to the left without deleting the first nonzero.
View Replies!
View Related
Message To Appear Prior To Printing Based On The Description Choosen
I have a list of products that I want a message to appear prior to printing based on the description choosen. Product descriptions are choosen in D25:D34 via a drop down. My wild cards are SWING GATE and DOUBLE SWING GATE, I want to use wild cards because of the many different sizes of each gate. Private Sub Workbook_BeforePrint(Cancel As Boolean) If Range("B" & Target.Row) = "swing gate" Or Range("B" & Target.Row) = "double swing gate" Then_ MsgBox (Range("F5").Value) & ", Please include chain and locks with order", vbOKOnly, "Chain and Locks" End If End Sub
View Replies!
View Related
Generic Formula Referencing Prior Tab/worksheet
The workbook has a tab for each day, and there are variance columns. The tabs are named for the date, like 1.20, 1.21, 1.22, etc. For example, the formula on 1.21 in D2 is simply =C2-'1.20'!C2 That works fine, but it's a time-consuming job to always change it. You have one sheet to copy many times for all the other formulas, but then have to re-name and change that variance formula on each worksheet. Is there a way to reference the prior worksheet without using it's specific name? =C2-'prior worksheet'!C2 would be nice...I could take my one sheet and copy it about 23 times for each workday, then copy that whole workbook to use in the subsequent months without making any formula changes.
View Replies!
View Related
Code To Check For Text In A Range Prior To Email Sheet
I am using the code below tied to a button on the worksheet to email a sheet and would like it to check that there has been text entered intothe cells below prior to emailing the sheet. If the cells have no text entered i would like a message box to pop up stating that all fields need to be completed. Range of cells: H6,A9,F9,A12,F12,A16,A23,A26,C28,D30,D32,D34,A37,D39,F36,F28 Email code I am using:
View Replies!
View Related
Run Part Of Macro, Pause, Run 2nd Part
I have 2 basic parts to a Macro which need to be separated by a pause of 1 second, before proceeding to the next part of the macro. Then pause for 1 second, and loop. Application.Wait is no good to me as the spreadsheet must remain live, editable, and receiving updates from an external program linked via DDE. Below is the ...
View Replies!
View Related
Worksheet Change Macro Takes Too Much Time When Run With Update List Macro
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run. My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
View Replies!
View Related
Macro Running In OS X, Able To Run This Macro On 11 Files At Once
I have this macro that a friend of mine wrote on a windows machine, and I can't quite figure out the code changes needed for OS X. For someone who knows how to do this, I'm sure it would only take a second, but I've been banging my head against the wall on this thing for too long. Option Base 1 Sub Data_Compiler() Dim FileSelected Dim FileName Dim CompiledDataArray(1 To 30, 1 To 53) ' Array to store our statistics Dim Counter1 As Integer Counter1 = 1 FileSelected = Application. GetOpenFilename("Your Files,*.xls", , "Select Files", , True) If StrComp(TypeName(FileSelected), "boolean", vbTextCompare) = 0 Then Exit Sub For Each FileName In FileSelected Application.DisplayAlerts = False................ I can get a "open files" dialogue box to pop up, but I can't figure out how to make sure that multiselect is enabled. I need to be able to run this macro on 11 files at once.
View Replies!
View Related
ActiveCell
Here's the situation: I want to search through the cells in a column to locate text in one of the cells. If this text is found, I want to make that the ActiveCell and then insert a row underneath it. If this particular text is not found, I want to insert a row and put that text that wasnt found into the first cell in the created row. I trimmed down my code a lot so I could post it here. So, in this particular case, once the user selects one of two product lines (named "ADC" and "DAC"), I want to first search for that text and if it is found I want that to be the new activecell and insert a new row. The main problem is that I can't seem to figure out how to set the "Foundcell" as being the new active cell. My initial activecell is set by locating and selecting the cell containing the text "Product Line". Here's my
View Replies!
View Related
ActiveCell.Offset
I want to use the following command but with more to it: X=0 Y=0 Z=10 ActiveCell.Offset(rowOffset:=X, columnOffset:=Y).Activate I want to use this type of command to highlight an area...not just move rows/columns. I want it to highlight from a point on the sheet marked by X and Y and then down Z rows. So if I'm in cell A1 and want to highight from A1 down to A10, I'm not sure how to write that part of the code.
View Replies!
View Related
Looping With ActiveCell
I am working on a sub that will populate a cell based on the values of two other cells. The ws is of variable length, the columns are A through I. I have the logic done, now I need help with the looping. It only loops through the first row. I believe that my problem is something to do with declaring the ActiveCell and looping with that. Also should I use variable names rather than Range(A2) ex. Dim EMUNCD As Variant EMUNCD = Range("H2").Value I have the following code so far Sub MainMgrRpt() Dim LstRow As Long LstRow = Cells(Rows.Count, 1).End(xlUp).Row For Row_counter = 2 To LstRow Dim planCase As String planCase = Range("F2").Value
View Replies!
View Related
Losing ActiveCell Value
I have a list of line entries for which I need to insert a variable amount of empty lines per line. (the variable amounts listed in a column to the right of my Active Row. Unfortunatly I loose the ActiveCell's Value. During Debug it shows the correct cell and value, but after the Debug Step Over, the value shows 0 again. I include my [code] Sub IP_Insert_rows() ' ' IP_Insert_rows Macro ' Macro recorded 2006/05/29 by Joseph Clark ' Dim GrpCtrVar As Integer Dim LnCtrVar As Integer GrpCtrVar = 3 LnCtrVar = Range("B8").Value
View Replies!
View Related
Mouse Relocation To ActiveCell
I've got an application where I switch between workSheets and the mouse stays in the same location. As all of my actions are predicated on mouse right-clicks or double-clicks, the first thing that I have to do as move the mouse back to the active cell. Is there a way to reposition the mouse to the activeCell upon returning to the workSheet?
View Replies!
View Related
Way To Use ActiveCell In Median Formula?
In Excel VBA, is there any way to use ActiveCell in Median Formula? E. G. Range("A5").Formula="=Median(ActiveCell.d5). Say ActiveCell already defined as Range("D2").Select or Activate. VBA doesn't accept this method. Any other way to use other than R1C1 direct address in the use of Median formula?
View Replies!
View Related
Copy ActiveCell OnChange
I am trying to copy the active cell from a range on sheet 1 to a new sheet ( Sheet 2 )in the next available row of Column B ONLY if the value changes from being blank. If it was not empty I dont want it to be copied. I cant seem to get my onChange event to copy anything.
View Replies!
View Related
ActiveCell And Command Button
I have a spreadsheet that automatically analyzes the last week's worth of data and then displays a table of summary data from that analysis in the range C2:G11. Each row (i.e. 2:11) represents the statistics from one of the 10 regions in the state. I then have a second "details" table which remains blank (it occupies the range C14:G20; one row for each day of the week) until an "x" is typed in Column H in the cell adjacent to a region's summary data above, e.g. if I want to display detailed data for region 4 for the last week, I would type an "x" in cell H5; formulas in the blank "details" table then lookup information on region 4 from a larger dataset. This system works fine, but is a little clumsy (typing "x"'s and deleting previous "x"'s, etc.). Here's what I would like: 1): A macro for this sheet that will place an "x" in the ActiveCell (only in the case that the active cell is somewhere in the range H2:H11), will remove that "x" when that cell is no longer active, and will not place more than one "x" at a time-- for instance if more than one cell is accidentally highlighted 2): Also, I would like to be able to paste the values and formatting of the "details" table (C14:G20) to a separate sheet (we'll call it Sheet2) if they're interesting, and I'd like to be able to do this with a command button. The problem is I don't know... 1: how to place a button on the sheet, 2: how to attach code to this button, 3: where to place this code (can it go in the same place as the ActiveCell macro I described earlier?). Ideally, the code would allow me to paste an arbitrary number of these tables to Sheet2, so the code would need to be able to identify the first blank cell in Column A as the location to paste a new table when the button is clicked.
View Replies!
View Related
Activecell/deactivate A Cell
is it possible to deactivate an activecell? the following code works well i have a range of cells(e1:o17) i click on one of those cells to enter a score into (a2) the next cell i click enters a score into (b2) ok so far but if i need to enter the same score consecutively as needs to be done from time to time i cant do it because the cell is still active. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("d1") = Range("d1") + 1 If Range("D1") = 2 Then Range("a2") = ActiveCell.Value: Range("c2") = 501 - Range("a2") If Range("D1") = 3 Then Range("a3") = ActiveCell.Value: Range("c3") = Range("c2") - Range("a3") If Range("D1") = 4 Then Range("a4") = ActiveCell.Value: Range("c4") = Range("c3") - Range("a4") If Range("D1") = 5 Then Range("a5") = ActiveCell.Value: Range("c5") = Range("c4") - Range("a5")
View Replies!
View Related
Delete TextBox That Is Placed On The Activecell
How to select the TextBox that is placed on the activecell without referencing to the TextBox number i.e ( TextBox1 ) I reached this code, but it is with the textbox number………sometimes I want to delete the TextBox that is placed on the activecell, so the user doesn't know the number of the textbox that is placed on the activecell……… ActiveSheet.Shapes("TextBox1").Select Selection.Delete
View Replies!
View Related
Have Macro Determine Which Macro To Run
Here is the scenario. I have a template that other users fill out and I run a macro based on the data that they populate in the template. I have three destinations for this data depending on how a particular cell is coded (validation list box with three choices). Currently I have to determine the how the cell is coded and run the corresponding macro. I would like to have one overall macro that can look at the cell with the coding and determine which of the three macros to run and then execute that macro. I was originally thinking of something like an If/Else statement, but that will only work if there are two options. Is there something else that I could use that will make this work?
View Replies!
View Related
Change Activecell Of An Inactive Sheet
CopyPaste operations leaves the goal range selected and I don't want that. The .Activate and .Select methods seem to require that the cell to be selected refers to the ActiveSheet. What I'm doing now to change the active-cell is kind of: 1. Disable ScreenUpdating 2. Save a reference to ActiveCell 3. Activate the relevant goal-worksheet 4. Change the ActiveCell to the upper-left-cell of the Selection 5. Come back by the reference in point number 2 6. Enable ScreenUpdating. It works, but seems to me as a huge job for something as simple than "collapsing" a multi-cell-selection. Another irritating problem is the slight screen-shaking caused by the disabling/enabling ScreenUpdating.
View Replies!
View Related
Activecell Reference From Command Button
I have about 200 command buttons on my spreadsheet. Since there are so many of these, I would like to create a VBA routine to determine the cell name four cells over from the cell each command button sits inside, if possible (each command button is small and sits within its own cell). So the command button in cell F1, will let me pull the data or get the cell name of cell C2. So I guess I am trying to figure out how to do relative references from a command button. Then this will be put in a public variable and passed to a subroutine which is the same for each command button. Only the data in the cells relative to the command button changes. I thought activecell might be useful for this, but you when press a command button, it doesn't make that cell the active cell. I just thought there might be a simple solution for this, instead of having each single command button have its own subroutine just so each one can reference cells. All I really need is to run the same subroutine with variables from 200 cells, one at a time.
View Replies!
View Related
Get ActiveCell Data From Inactive Sheet
I need to grab data from one sheet while I'm in a different one - same workbook, however. From the first sheet, the data that I need would be an "activeCell.value" issue. From the other sheet, however, how can I get that cell's data - an "inactiveCell.value" call, in essence? Right now, I'm getting around it by assigning the value to a global variable but that seems wasteful.
View Replies!
View Related
Userform Text Based On ActiveCell
As usual, I think this might be a really simple one but I can’t seem to find the answer. I want to create a userform that has text at the top (not the title bar) that looks at the value in the ActiveCell. e.g. "Please enter information for " & ActiveCell I have tried to place this in a text box and the code for the text box but nothing seems to work I am pretty new to userforms so could well be going about this completely the wrong way.
View Replies!
View Related
ActiveCell.Offset - Copy Range
Can i convert this ActiveCell. Offset(-1, 0).Copy With ActiveCell .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .ClearOutline End With So that i can copy/paste the Offset range such as (-1,-2) (-1,1)?
View Replies!
View Related
Using ActiveCell.Offset To Copy Between Worksheets
I created macro that copies cells from one worksheet to another using the copy/paste function. Unfortunately it runs rather slow. Instead of copy and paste I want to use something like this: ActiveCell.Offset(0,1)= Sheets("Raw Data").ActiveCell.Offset(2, 3) Can this be done and what is the proper syntax?
View Replies!
View Related
Proper Syntax For Activecell Address
I want my users to be able to double click on a cell and based upon that cells address or range I want another sheet to be activated. Im currently trying to do something like this Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True If ActiveCell.Address = ("a10") Then Sheet2.Activate End Sub It does not matter to me what specific thing about the currently selected cell is used in the if then statement, but it cannot be the cells value as several cells have the same values. Am I on the right track and just bad syntax or am I way off base here?
View Replies!
View Related
How To Run A Macro
I am trying to figure out how to run a macro that will do a few things for me. 1. it needs to compare at least 4 fields of data against one and eliminate the fields that are totally out of whack (i.e., we have units of measures in products that are not consistent, so I want to remove the obvious issues of UoMs and keep the others that seem to be comparing the same type of units of measure). 2. Once it eliminates the bad data, I would like to create another spreadsheet with the good data in it, copying only the data not all the other macros that exist in the spreadsheet. 3. I would like for this macro to then take out the material number of the bad data from the appropriate worksheet(s), 4. what I would like to be left with is a clean sheet of data that is comparing apples to apples not apples to a crate of apples.
View Replies!
View Related
Run A Macro Every Second
I want to run a macro every second do I need to do something like that? Sub Auto_Open() Application.OnTime Now, "myMacro" End Sub Sub myMacro ... mdNextTime1 = Now + TimeValue("00:00:01") Application.OnTime mdNextTime1, "myMacro" End Sub but this creates a kind of blinking in excel because it copies things from a Sheet to another one.
View Replies!
View Related
How Can I Run A Macro
I have part number report that exports to excel. The part number is column "A". There is a character ( ' ) in front of every part number. Can I run a macro that will take that column "A" and format it to text and remove that first character in the cell?
View Replies!
View Related
|
TRACKER
