Macro To Return To Where User Was Working
May 29, 2008
i have an excel doc. with three sheets...
i may start that macro on any sheet any cell
the 1st thing that macro does is to go to sheet1 and after running the whole code it calls another macro... macro2
now macro2 runs in an infinte loop and runs evry 20 sec. the problem is every 20 sec the cursor comes back to sheet3.. i want the macro to return to the place where the user was working.. best wud be that the user can work seem lessly...
View 9 Replies
ADVERTISEMENT
Sep 10, 2006
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 5 Replies
View Related
Dec 1, 2006
how do i get the previous days working date in VBA, how can i get VBA to determine wether we are in the week, and how will i get it to know its a monday to retrieve fridays date?
View 3 Replies
View Related
Feb 21, 2014
I am trying to create a macro that will compare several un-formatted workbooks and copy just what I want to a workbook of my one.
I start by having 2 different workbooks opened ("Main" and "Change") side by side like this: 1.jpg
Then i run the macro in my "Main" workbook and it should appear an Userform like it showed in the image below: 2.jpg
This is so that i can manually fill the user form with the correct columns by looking at the excel file, for example:
Ref=E3;City=G3; Data=I3.
Pressing Ok, it fill the "Main" workbook with the matching information so it and closes the "Change" workbook.
At the moment my code is in this stage:
VB:
Private Sub CommandButton1_Click()
myfile = "C:Userssst1brgDesktopRelatorios" & TextBox1.Value
Application.Workbooks.Open Filename:=myfile
Windows.Arrange ArrangeStyle:=xlVertical
[Code] .....
But I am stuck because there isn't any error and nothing happens.
View 1 Replies
View Related
Oct 22, 2007
I attempted to create a user function as per the link that VBA Noob posted in this post http://www.excelforum.com/excel-worksheet-functions/619446-resolved-play-a-sound-when-a-cell-equals-a-certain-value.html
I get #Name error. I placed the wav file Applause.wav in the same folder as the workbook. What did I do wrong?. Also, assuming I get this problem sorted out, how would I represent the condition "pass" or "fail" as in cell B3 in the Alarm formula? Would it be; Alarm(B3,"Pass") or Alarm(B3,"=Pass") or something else?
View 5 Replies
View Related
May 26, 2009
I have created a workbook ( with protected sheets) containing two macros " To Insert a Row ( Copying the formula from the row above) and "Delete a Row", both, assigned to a customized toolbar.
when I move the workbook, say from the folder where I created it originally to the desktop, the macros stop working giving error like THE MACRO " C:so n so... CAN NOT BE FOUND!
View 9 Replies
View Related
Apr 20, 2009
I am trying to display a message box for the user if there is "agency" in cell o8 but nothing in p8... I tried the following code but it doesnt work..
If Range("o8").Value = "Agency" And Range("p8").Value = "" Then
MsgBox "Please provide name of agency in cell p8"
Sheet9.Shapes("cross").Visible = True
Else
View 9 Replies
View Related
Apr 3, 2014
I think this may be impossible but what I am hoping to do is in B3 Return the date that is 3 working days before the date in B2. Working days being Monday to Friday in this case. so if the date in B2 is the 16/04/2014 3 days before this is the 13/04/2014 which is a Saturday so i would want the formula to return the 12/04/2014 which is the Friday. Is this possible?
View 2 Replies
View Related
Jul 22, 2007
i have been looking everywhere to find a way to get user info.
in excel, is there a formula or macro so that when saved, the user info is put in the file without the user putting their own name or id?
View 9 Replies
View Related
Nov 26, 2009
how to return value from VBA User Defined Funtion
View 9 Replies
View Related
Jun 13, 2013
I have a formula to find if the Symbol "!" is contained in the a cell. And i want it to input "Undercut Due to Trim Edge" if there is a "!" symbol and blank if there is not. What did I do wrong?
=IF(ISNUMBER(SEARCH("~!",AL57:AV57)),"UNDERCUT DUE TO TRIM Edge","")
View 3 Replies
View Related
May 4, 2008
The format for the sheet is set in stone and can't change, and therefore my macro's operate on set columns / rows amd cells.
Because the sheet is huge, im trying to figure out a way to only run the macro's that need to run and more specfically on the changed location.
The Question
I need to create a routine that on the user changing a cell, it returns the cell position (row,column), then it will call a routine i have already which decides based on the cell location, which macro to run and where.
View 9 Replies
View Related
May 2, 2012
I am working with a fiscal year that starts in December and ends in November. I want to make a user defined function that will return the fiscal year of a date. I've created the below code, but it returns a zero.
Function FiscalYear(DateFY)
If Month(DateFY) = 12 Then
Year (DateFY) + 1
Else
Year (DateFY)
End If
End Function
View 3 Replies
View Related
Jan 15, 2014
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
View 9 Replies
View Related
Nov 6, 2011
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
View 3 Replies
View Related
Jul 16, 2007
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
View 2 Replies
View Related
Oct 9, 2012
I have recorded the following marco and it works fine. I would like to modify it so the user would highlight a range of cells rather than have the fixed area :
"'ConsExecProp@20120920'!R4C5:R113C21"
What code lines would need to be added?
Application.Goto Reference:="R4C5"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
Array("'ConsExecProp@20120920'!R4C5:R113C21"), Version:=xlPivotTableVersion12 _
[code]....
View 4 Replies
View Related
Jan 27, 2014
I am trying to write a sub for using vlookup. To enable the vlookup, I want to return a word in a cell to the cell in the next column. I use the InStr function, but it doesn't work. They all return as "Other".
Attached is the worksheet and here are the codes : Capture.JPG
[Code] .....
View 11 Replies
View Related
Jan 15, 2009
Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date
But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero
Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!
View 7 Replies
View Related
Feb 19, 2009
I'm creating a macro that creates a co-ocurrence matrix from variables that are adjacent to each other.
In order to proceed, I need to know how to return the column number of the first (leftmost) column in a range that the user selects.
View 2 Replies
View Related
Feb 7, 2014
The attached workbook contains a macro ("Transfer_Data") that copies values from one cell to another when the "Update Progress" button (near cell A3) is selected. It works perfectly fine in this workbook but when I try the same code in another workbook, which looks and operates exactly like this one, nothing happens.
Is this due to a security setting or missing code line items?
copy&paste in next blank cell2.xlsm
View 10 Replies
View Related
Jan 28, 2014
Below is a code which I added to my 'Quick access toolbar' (and which is based off a hidden workbook). I use this macro to fulfill tasks to regular excel spreadsheets that are not macro enabled, however, I can't seem to get this macro to work for other workbooks. How to modify my code to work for other spreadsheets.
[Code] .....
View 4 Replies
View Related
Feb 2, 2014
It gives me a error and says my password is wrong. I have user names set to return the password so that it is unlocked for certain people. It works for unprotecting the sheets. I modified it and added it back in to try to do it for the workbook.
[Code] .........
I realize that they " and () around the password are differant, but that's how I last left them. I have tried several combinations and it just gives me more errors saying expected end of statement, etc.
View 8 Replies
View Related
Sep 2, 2008
I am using this code in a macro to find a substring from a string in cell G1.
View 11 Replies
View Related
Dec 2, 2011
I have this macro which sits in the workbook module:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
[Code] .......
It's used to highlight the selected/active row.
But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:
Code:
Sub Send_Mail()
'this sends an email that sends a text message
Dim OutApp As Object
Dim OutMail As Object
Dim MyButton As String
Dim strTo As String
[Code] .......
I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?
View 5 Replies
View Related
Jan 15, 2009
I've got a semi-recorded macro to format a report I run, and towards the end it deals with sorting, subtotalling, formatting text etc. The main function of the macro is to split a large report into several smaller reports defined by whichever manager name appears in the relevant column. The smaller reports need subtotalling by 2 different columns so I have the following macro code :
Cells.Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
For some reason this only subtotals by the second column and not the first. I tried swapping them over so GroupBy1 then GroupBy2 but it's still the same. Anyone got any ideas why this might be? I appreciate it's not the smoothest code I could use but I don't know enough about code to do any better.
View 9 Replies
View Related
Jul 7, 2009
Have recorded a Macro which is doing a great job, but when i try to run the same macro from other pc its not running,pops an search error.
View 9 Replies
View Related
Jul 22, 2009
I've got a column that auto-populates from anther column depending on what's input, and that's working fine. I also have a button set up to select and copy the auto-populated data. I just recorded myself select the top cell and hit shift+ctrl+down to select just the values that are populated. What it does, however, is selects the blank ones too. I know the counta() function would work, but I don't know how to input that into a macro.
The macro I have now is:
Sub Copy()
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End Sub
The column is only 25 rows tall.
View 9 Replies
View Related
Feb 17, 2010
I have a macro to export two worksheets and save as, but the save as part will not work, it just exports to Book1.
Ive checked the directory and everything is fine.
I want the value of a cell in one of the exported worksheet to be included in the save as name.
I'm just wondering if the code needs to somehow specify the worksheet to get the cell value from or will it default to the first worksheet. but either way my code is still not working and i can't tell why.
Should i just create a seperate macro for the save as?
Here is the ...
View 9 Replies
View Related
May 10, 2014
I got a problem with a macro i'm working on. I got column D which contains text as "Figures", "Toys", "3DS", "PS3" etc. I also got column N where i need the macro to display "Toys" if it's a toy, figure, etc; or "Games" if it's "PS3", "PS4" etc.
I'm using the following code, to also select only blank rows (so to ignore row 1 which is table header). Problem is that the result shows only "Games".
VB:
Columns("N:N").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(RC[-9]=""*toys*"",""Toys"",""Games"")"
View 4 Replies
View Related
