I have a named Formula which works but it will only recalculate when I click the green tick in the Formula Bar. How can I get it to work without needing to do that?
View 9 RepliesI have an interesting question for everybody here. Let me see how to best explain it.
I start in the code making sure column Action Plan completion has a value in it. Copying in the issue modified column if neccesary.
'Back fill last modified
'I need to start with the first data row (3) and the action plan completion column
Let intcurrentrow = 3
Let intcurrentcol = intactionplancompletion
Do Until intcurrentrow > intmaxrow
If Cells(intcurrentrow, intcurrentcol).Value = "" Then
Let Cells(intcurrentrow, intcurrentcol).Value = Cells(intcurrentrow, intissuemodified).Value
'Need to ensure that everything is formatted as a date
Let Cells(intcurrentrow, intcurrentcol).NumberFormat = "m/d/yy h:mm AM/PM"
End If
Let intcurrentrow = intcurrentrow + 1
Loop
Later, I place this formula in a given cell.
=If(I9="Open-Remediated",M9-K9, If(I9="Closed-Remediated and Validated",M9-K9,"" ))
The info I copied above is placed into the column K.
When I run the macro, the cell formula outputs "#value".
However, if I go double-click then enter the cell calculates just fine.
I've tried excels overall recalculate function, but that doesn't change this.
Does anybody know how to simulate a double-click on a cell?
I was reading your Tick Cell Upon Selection article and it works great. I'm just wondering if there is a way to modify it so that they can only select one of the cells in a row.
For example, in this spreadsheet, the cells I have marked for "tick upon selection" are N8-Q8. In this worksheet, though, I only want the user to be able to select one of the four options. Is there a way to format the code so that they either cannot select another cell until they have deselected the first one, or the first one unchecks itself and the new click ticks that cell?
Example: Person selects N8. Person then selects P8. Can it not allow P8 to be selected (and give a warning message) or can it uncheck N8 when P8 is checked?
how to lock a formula in a cell, protect the sheet but still allow it to recalculate when new data is entered in a unlocked cell that feeds the formula? I am using Excel 2003.
View 5 Replies View RelatedI have made some UDFs (custom functions) using VBA, when the problem is that they don't update automatically like standard functions. I have numerous tickboxes which cause text to become bolded, and then my function counts the total, only including bolded text:
Function CountGamerScore() As Long
Dim i As Integer
Dim strScore As String
Dim intDigit As Integer
i = 6
For i = 6 To 52
strScore = "L" & i
If Range(strScore).Font.Bold = True Then
intDigit = Left(Range(strScore).Value, Len(Range(strScore).Value) - 1)
CountGamerScore = CountGamerScore + intDigit
End If
Next i
End Function
However, if I untick or tick a checkbox, the bold and unbold works fine (done in another sub, but the function does not update the value in the cell for total.
Therefore, is there any VBA code that will tell Excel to recalculate formulae??
I'm trying to build a price index in which when a user changes the value in any one cells, the other cells on that row would change dynamically. In other words, if a user enters 25% in the "discount %" column, the "net price" column would reflect this discount ( attached is a sample sheet). I thought I had this covered but ran in to some problems I can't figure out on my own.
The worksheet_change function only inserts a formula in to a cell once. After a value has been added to that cell manually, the function doesn't insert the formula in it again. Also, there are empty cells inside the price index and I don't know how to take them into consideration in my code. And for last, if, and only if a discount % is entered a phrase "Z106" should appear in the last column. But if any of the net prices are manually entered, a phrase "ZSOP" should appear in the last column.
I have attached a small file with a very simple conditional formatting formula used to turn a block of cells green.
However, the formula does not seem to work properly as it only turns half of the cells green. Columns B1 to B18 are also supposed to turn green, but instead remain clear.
Some sent me a large spreadsheet with random rows throughout the spreadsheet highlighted in diffierent colors. I have a module that will sort the spread sheet by Color - however it doesn't work on this spreadsheet because - for whatever reason - every cell starts with a tick mark.
I tried to do a replace all - and excel just told me I was crazy.
I even tried going through and manually removing the tick marks - still no luck.
If I export the sheet in to txt and then re-import it I will loose all the highlights...
I have this piece of code that shows a popup box when the excel spreadsheet loads up.
Private Sub Workbook_Open()
MsgBox "This spreadsheet can design both single-leaf and cavity walls." _
& Chr(13) & Chr(13) & "If only a single-leaf wall is to be designed:" _
& Chr(13) & "Deselect the cavity wall option and complete only the outer leaf input sheet." _
& Chr(13) & Chr(13) & "If a cavity wall is to be designed:" _
& Chr(13) & "Select the cavity wall option and complete both input sheets." _
& Chr(13) & Chr(13) & "All designs satisfy criteria within BS:5628-1:1992 Structural Use Of Unreinforced Masonry", , _
"Spreadsheet Information"
End Sub
Is it possible to place a tick box in the message box that says, "show this message again on startup", then if the user unticks the box the message is not shown again and if the box is left ticked the box will load up again on startup of the workbook????
I'm creating a form, that will contain 20-30 tickboxes. Each of these tickboxes will refer to a certain row on a seperate sheet. And it will either hide/un-hide depending on if it is tick/un-ticked.
How can I in the macro know which of the tick boxes that was ticked?
I have created a Time Sheet for calculating the work hours of employees. There are various criteria which play a role in how work hours and wages earned are calculated (ex. time, over time, LOA, Travel time, stat holidays etc etc.)
In a certain cell I have placed a tick / check box which the user can tick to indicate that that particular day is a statutory holiday. The tick creates a True / False answer in another Linked Cell.
Based on the True / False result I have a formula which, using the IF function with multiple conditions whether the result is True or False, will calculate the hours worked for Regular, Time and Half and Double Time.
The Formula itself works but when I add the condition based on the check box - example: IF(c37=TRUE,...,if(...,if(...,IF(c37=FALSE,...,if(...,if(... and so on, only the TRUE option works. As soon as the check box is "unticked" I get the result "false".
I hope that all makes sense.
If it helps this is my formula... where C37 is the linked cell for the checkbox.
=IF(C37=TRUE,IF(B10<4.1,B11,IF(B10<8.1,4+(B10-B11),IF(B10<12.1,B10-B11+4,IF(B10>12,8,IF(C37=FALSE,IF(B10<8.1,0,IF(B10>8,IF(B10<12.1,B10-B11,4)))))))))
So, when the check box is ticked, the TRUE argument works fine. But when the check box is not ticked I get a "false" result.
Just put condition formatting on a cell that looks up to Windings tick & X
Now with the listing option in condition formatting
you get the option of a dropdown on the cell you are populating.. great but this is still in the worksheets standard font so shows as an O or P not a tick or X.. any way round this?
I would like to achieve the affect where if you click on the cell its contents will change. For example, if you click on it once, a checkmark will appear, but if you click on it twice and x and if you click on it a third time a - will be displayed.
I was thinking along the lines of a marco for the spreadsheet, which would run an if loop to check what was currently in the cell then change accordingly. Is there a command that would allow me to do this, or would is there an even easier way? side note how would I even display a checkmark of square root sign, through vba? This is what I have, It was working but now it does nothing and I cant remember what I tinkered with.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 6 Then
If Cells(Target.Row, 6) = "" Or Cells(Target.Row, 6) = "-" Then
Cells(Target.Row, 6).Formula = ""
Application.EnableEvents = True
ElseIf Cells(Target.Row, 6) = "?" Then
Cells(Target.Row, 6).Formula = "x"
Application.EnableEvents = True
Else
Cells(Target.Row, 6).Formula = "-"
Application.EnableEvents = True
End If
End If
Application.EnableEvents = True
End Sub
I have a countdown formula in a cell and I would like to set up code to recalculate the countdown timer.
View 14 Replies View RelatedI built the below udf to run a calculation on a range based off of dates. But it won't recalculate when the cell with a date changes.
I had to do the "application.volatile" at the top and press F9 for it to work.
Is there a way to recalculate without this or trick excel to do so?
Function TestRange(r As Range, q As Range, rcol As Range)
'Selects range based off two specified date ranges
'formula must reside in the same column as the data column (at the top)
Dim rlast As Range
Dim firstvalcell As Range
Dim Lastvalcell As Range
[Code] ......
I have a macro that can individually turn on or off the EnableCalculation property of each worksheet in a workbook (that is, set the property to True or False). It worked very nicely for a while, and made some of my more formula-laden workbooks much more tractable. But after a while, when I would turn the EnableCalculation back to True for any worksheet, the formulas on that sheet were still frozen (would not recalculate at all), and could only be unfrozen by going to each cell and doing [F2] [Enter]. Just to rule out the obvious, Automatic calculation is turned on, and I have made sure that the macro is successfully returning the EnableCalculation property back to True.
It seems that if I physically go into the VB Editor and manually (through the GUI, as opposed to programmatically) switch the EnableCalculation property back to False, then back to True again, then it will revive the formulas. But this is silly, and I was hoping there was a better solution... maybe someone has encountered this before? If it's just another Microsoft bug, I don't know what's the trigger is, but I imagine it's when you save/close the workbook with some of the sheets' EnableCalculation properties still set to False. I know the property is not stored/saved with the workbook, i.e. all sheets will reset EnableCalculation to True when the workbook is re-opened, but maybe it's still causing a glitch.
My excel file consists of 15 sheets, thereof 4 that are purely graphs, the others have tables with formulas. When opening the file or changing values, the entire sheet is being recalculated every time which takes a few seconds. Is this normal or can one tell to recalculate at another stage?
The activities sheet has a date macro (double click in date cell).
The workbook has a macro for the footer information.
My sheet has a very simple formula in a lot of the cells that simply say this cell in sheet A should equal a cell on sheet B in the same workbook.
I deleted the contents of the cells in sheet B, however the cells in sheet A did not update. It still shows the contents of sheet B that were there before I deleted them. The sheet is set to calculate automatically and I even commanded it to force calculation.
Any clues as to why my sheet will not update?
If I cut a formula from a cell in Sheet A and paste right back, it updates correctly to reflect the cell that was deleted in Sheet B.
I wonder if it is possible to make a macro that right clicks a cell and then chooses a option from the list?
View 11 Replies View RelatedI assume I would need a macro, but I am wanting to have a button where-by the user clicks to create another 100 rows and in-turn, continue the formula's that reside within the columns and rows above, down...
View 2 Replies View RelatedI am new to excel and need a formula for conditional formatting. In E1 I will be putting a date for the month and in cell J1 I want it to highlight green if there is a date in E1 and if there is nothing in E1 I want it to be red.
View 9 Replies View RelatedI am trying to create a formula that looks at a spreadsheet containing training percentages for employees. I need to look at these percentages and based on the date that the person started I need to look at certain rules. First it needs to look at what has happened more recently , moving to a new department or promotion. Next it needs to use that info to decide which rule to use. Ex. the person started 90 days ago then they are subject to rule A. The training percentage then needs to be looked at to see what rating they have. EX. percentage < 90% but > 75% gives a rating of 1. percentage than 50% gives rating of 2. percentage < than 50% rating of 3. Rule B would contain different criteria. My problem is that I tried nesting IF statements but I need more than 7. When I tried to do this in VBA I couldn't get it to work.
View 9 Replies View RelatedSomehow, from a fresh instal of Excel (to try and cure the same problem), and Insert Module, the box is green. Viewing a macro, the background is green, some of the code is black on green, but most is black or blue in white boxes cut out of the green. The code appears normal on another computer.
View 7 Replies View RelatedSo let's say I have a table like this:
Apple
Banana
Cherry
Banana
Durian
And when I tick one row:
Apple
Banana
Cherry
Banana
✓
Durian
The rest of rows of same values will also be ticked:
Apple
Banana
✓
Cherry
Banana
✓
Durian
I would like to have the macro/VBA for this.
This question was also posted at:
ExcelForum: [URL]...
MrExcel: [URL]....
I have set up a spread sheet which uses tick boxes and I have calculated it all up so that the percentage of ticks ticked is displayed in a cell etc. and when I link that data to a bar chart it works perfectly. .e.g as I tick the boxes the bar chart increases
BUT when I try to do the exact same thing with a pie chart it doesn't work.
I need to click on a cell and when I click on it, it should change colour and insert a tick (or other symbol).
View 14 Replies View RelatedI have 4 checkboxes: a, b, c, and d. if all three check box a,b, and c is checked, then check box d is checked. if any of a, b, or c is unchecked, then check box d is unchecked.
View 3 Replies View RelatedI have set up a spreadsheet but need to add a tick box, I just need to tick it once the item has been completed.
View 6 Replies View RelatedThey have 50 members and if 35 of them turn up to the meeting I want to be able to print out a 35 certificates each with a name on it from the people that attended
Almost like a mail merge from the full list of 50 members but only print out for the people that attended >?
I was going to tick or enter a 1 if the person turned up and then have it pull all the names with a 1 next to it ?
My situation so far i have a rather large program in vba that accesses a site (confidential info im afriad will post snippets though) logs in navigates through a rabbit warren of pages using different types div's form's a href's etc and finally get to one page before where i need to extract information from!!
This webpage has one large table then nested tables within for each entry.
For example
PHP Code:
<table><tr><td><table>info....</table></tr></td><tr><td><table>info....
</table></tr></td><tr><td><table>info....</table></tr></td></table>
I need to find a way to search the data cells within the nested tables find a specific displayed value then if required tick the checkbox (which has dynamically generated id's names etc) in that row then move on to the next table and do the same.
All I need to do is establish that one row has the data value i need and tick the check box that correlates (which is in the same row).
PHP Code:
<table border="0" cellpadding="0" cellspacing="0">
<tr class="altline"> <td> <table> <tr> <td class="col_time" title="16/04/2013">18:30</td>
<td class="col_duration">00:51</td> <td class="col_placetype"></td>
[Code] ...........