I am looking to identify when
a) New comments are entered in cells
b) When changes are made to existing comments
c) New cell entries
d) Changes to existing cell entries
I have mocked up a typical example between sheet 1 and 2. I have found tools that will identify cell entry changes (i.e. c & d) but not on cell comment changes (i.e. a & b).
I am trying to put together multiple userforms that perform separate tasks. I have been using code found on this forum, and it's helped out a lot, but i'm still having trouble on one of the userforms. the one that i'm having the biggest trouble with called frmEditData. What it is designed to do is pull data from different fields and display them in the textboxes on the different sheets. then, the user has the option of updating those tabs and saving the changes. For the life of me, i cannot get the data to change. it always reverts back to the original information in the cells.
i have to say again that I don't really know how to read the code too well just yet, and i've been copying code that appears to work on other sheets, but it might not necessarily work here. I have attached a rough copy of what i've been working on.
Some of the sheets in my spreadsheet contain some data and graphs with a few macros that are run when a button is pressed. At the bottom I have some comments with a macro that is run to timestamp when these comments are updated or edited.
What I would like to do is lock the entire sheet so that the data and graphs can't be tampered with, apart from the comments part at the bottom because I'd like to be able to have the comments editable. So far I have managed to do that, but when I edit a comment and the timestamp code is run it creates a run-time error 1004. I could just remove the timestamp code I guess but I'd like to keep it there... figuring that there must be a solution.
How can I work around this problem ? Either by being able to keep the sheet locked and still have the timestamp code run, or have it temporarily unlock the sheet - run the code - and then re-lock the sheet.
When I click this button, a new row is inserted. Depending on whether this checkbox is checked, I want some of the formulas to adjust.
Public Sub AddEntry()
thisone = ActiveSheet. Name
FinalRow = Range("A65536").End(xlUp).Row
Range("A" & FinalRow + 1).Select
Selection.EntireRow.Insert 'just inserted a new row
x = Range("T1").Value
Sheets("Calculator").Select
Rows(x + 3).Select
Selection.Copy
Sheets(thisone).Select
Rows(FinalRow + 1).Select...........................
1. I dont want this to auto send. I want it to compose the mail and leave it ready to hit the send button in lotus notes. How do I do that?
2. Would like to include multiple emails. If I put two emails (separated by comma or semicolon into cell C19, Lotus notes sends as one address and it bounces. (This is not terribly important, but would be a nice feature)
3. Would like to insert the signature line that's already configured on lotus notes. Since this auto sends the email, the signature is not added. Any idea how to do this?
4. Now this would be really nice, tell the macro which database to use. The one I want to use is not "my" email, but a shared email. It lives on server "Notes1/recovery" , in folder "mail" with a database named "company.nsf". Is there a way to do this?
Here is my code that works, but needs the above features.
Sub SendNotesMail()
Dim Maildb As Object
Dim UserName As String
Dim MailDbName As String
Dim MailDoc As Object
Dim Session As Object
Dim Recipient As String
Dim Subject1 As String
Dim ccRecipient As String
I have a matrix that calculate lenght of the sides in a triangle, this is for the purpose of a camera and the pixelsize/inchsize.
Is it possible to create a chart that will give the user an visual control on how the cameras detection area looks like?
I seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.
I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.
Dim n As Double
n = Now()
sn = Str(n)
p = InStr(sn, ".")
first = Left(sn, (p - 1))
l = Len(sn)
d = l - p
S = Mid(sn, (p + 1), d)....................
If a user changes some data in a pivot table and then refreshes the table, the changed values are supposed to revert to the source data, right? This does not happen in my pivot table.
Any changes made in the underlying data get transferred to the pivot table refresh, as expected. But if I change some values in the pivot table, followed by a refresh, then the cells that I changed remain the same unless I happened to modify the corresponding values in the underlying source data too. In other words, the refresh operation does not touch the pivot table except specifically where an update is driven by a change in the underlying data.
This behavior is different from what i remember from, say, excel 97 / 2000.
I am creating an asset management sheet. For the formula I am trying to work out there uses 3 fields : ID, start date, and end date.
What I want to do is be able to show if the ID is duplicated within another record with an overlapping date. So an item is flagged if it is in the list within the same dates as another record. I tried a few countif formulas but with no success.. I may just be approaching the problem incorrectly though.
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.
I have a spreadsheet that feeds from a master list in excel, from over 5000 records.
I need to print the s'sheet with any given indivdual record's information at any given time.
Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.
Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?
The added piece I now need to put in is how many times a cell value is changed in a day (I will add to a macro to return it to 0 at the end of each day). The way in which I would approach this is by using a combination of a cell_change and count function. Could I ask somebody to advise if this is the best way?
View 7 Replies View RelatedWhen a cell changes i would like the cell in the next column to record the date of this change. After achieving this i want to set up conditional formatting to compare this date to the current date and if they are the same then change the original cell's colour to show it changed today!
I started using the code below (from ozgrid) which was working fine BUT my cell value in column A is actually taken from a different column. when the different column cell value changes my cell in A changes but the script below does not seem to see this as a change. it only records the date if i physcially change the cell in A rather than another cell prompting it to change.
based on my overall objective and my plans to date please can someone suggest a solution?
current code below....
I have an Excel worksheet full of data. I would like to search in a specific column for cell that contain "xyz". If found cell = xyz, then I want to delete the whole record or row.
Is there a formula or option to do this?
I am currently looking at each record and deleting it manually. With a worksheet of more than 2000 records, this become a huge task!
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.
I'm working on a spreadsheet that has a counting number value on the cell L2 and a score on cell L4. These cells will be changed during the day, L2 always increasing, but not with a uniform increment. The interval between the changes also varies. Each time L2 changes, L4 may or not change. I'd like to keep the record of the changes on L2 and L4, each one in a row, together with the date and time when the changes where made to L2 and only add one new row if the L2 value has changed. The records should start from line 6 on...
See the attached file for more details. Currently I'm doing the updates to the record rows manually, I'd like to make it automatic every time the value of L2 is changed from the previous value to a new one.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$2" Then
If IsNumeric(Target) Then
'Stop any possible runtime errors and halting code
On Error Resume Next
'Turn off ALL events so the Target * 2 does not _
put the code into a loop.
Application.EnableEvents = False............
The below code works fine except when there is no recorset found then i want to display 0 in the cell but its not doing it. The code just leaves it blank if no recordset found.
[Code].....
What I'm trying to put together is a sheet where i work out how much margin i will be making on a sale (which i done). The bit I'm stuck with is the recording the data on a separate sheet.
What i have done so far.
I created a Macro button to insert and copy cells C6, C7 & C8 (the bits i need). Which works but it takes the formular with it so everytime i chage the figure on sheet 1 it changes in sheet 2.
So what i'm asking is there a way to mirror what is display rather that copy?
How do I express in a formula if ce406= any one of the following results, then Yes, if not then No:
1 2 1 1 1 2
1 2 1 1 2 1
1 2 1 2 2 2
1 2 2 1 1 2
1 2 2 1 2 1
1 2 2 2 2 2
2 2 2 1 1 2
2 2 2 1 2 1
2 2 2 2 2 2
I tried the following formula, however, it did not give the desired result: ...
I am trying to create a continuously updating record or log of the numbers in a particular cell that has numbers that change frequently. I want every number that was every in that cell recorded in a table or column so I can analyze those numbers. Please note that I do not have much VBA experience.
View 9 Replies View RelatedWhat i would like to do is record a cell on sheet1 in sheet2 when it reaches a certain number i.e 2. The cell in sheet1 changes all the time but sheet2 must not refresh. Is this possible with a formula?
View 9 Replies View RelatedIm trying to record a macro that will enter a formula in a cell each time I run the macro
Drop the Lowest2:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10)
Drop Lowest1:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10)
I know I can write the formula in a simpler way, but I was asked to make it like this.
The problem is that when I enter the formula in the cell while recording. I got a message: "Unable to record" so this part can't be recorded
This problem seemed to revolve around "digital signing" with further macro changes done from a different machine (without the proper certificate). Excel warns of the problem and then "unsigns" the project.
I've got a problem with spreadsheets that I've been working on. Now, when I tell it to record a macro, I get the macro name, shortcut assignment dialog box and then an error "Unable to record." After "Ok"ing that dialog box, the normal recording macro dialog comes up. Also, lo and behold, there will not be a macro recorded.
I've got no protected cells or sheets and it doesn't matter what security level is set. It is a "signed" macro but I can take off the signature and still have the problem. I checked for "Help - About" for deactivated modules and there was none.
I've got working routines that do some action upon double-clicking within a specific range of cells. I want to be able to "wrap" this routine within a routine that will record the activeCell prior to the double-click and, upon completion of my routine, return to that prior location.
View 3 Replies View RelatedMS Excel 2010, WinsXP
how to highlight an entire record based on the value of a single cell?
I would like to highlight all records grey where cells in a column = "closed".
Using excel 2011 for mac and would like to learn how to write and record a macro to link to a button to "go to a layout and cell"
View 4 Replies View Related=IF(E14<=0,0,IF(N9="yes",MAX(E15*C15,30),30))
I am currently using the above formula and need to make an addition to it.
If D8 is greater than 9000 and less than 9999 then the entry will be 35 rather than 30. Any other entry in D8 would leave it at 30
i have managed to pull together some code that will deny people adding data into cells if they have 5 of the same entry. the entries are entered in a range and are matched against a single cell outside of the range. heres the
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim greycell As Range, i As Long
If Not Intersect(Target, Me.[grey]) Is Nothing Then
Application.EnableEvents = False
For Each greycell In Target
If WorksheetFunction. CountIf(Me.[grey], greycell.Value) > 5 Then
i = greycell.Interior.ColorIndex
greycell.Interior.ColorIndex = 3 'red
greycell.Select
MsgBox "no cell entry past 5", vbCritical, "ERROR"
greycell.ClearContents: greycell.Interior.ColorIndex = i
End If
Next
Application.EnableEvents = True
End If
End Sub
what i need with is adapting this code to match two ranges as i cant use the worksheet_change event twice. i need it to be as if they were seperate events but are merged together. eg:.............
How would I go about displaying in a cell the time from the last entry to the current but show 0 time if the range is empty?
E5 to R6 currently has space for numbers to be entered.
E3 has the command =NOW()
I would like E7 to show "xxx minutes have passed since last entry" where xxx would show 0 while the range is empty.
Colum A
Ticket No.
150258, 150268, 150269
150259
150249, 150266, 150271, 150279
Colum B
No. of Stillages
3
1
4
I have two colums. In A1:A4 are ticket numbers of products made. In B1:B4 are the number of stillages that the tickets relate to. What i am trying to work out, is a formula that will calculate the number of stillages produced based on the fact that each ticket number begins with "150". I have tried using COUNTIF, MATCH and FIND functions, but when i does work, will only return a value of 1.