Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing cell value update/refresh
I'm pretty sure I read the solution to this but after searching for ages
can't find it so sorry if this is a repeat... I'm placing values into a worksheet from a userform and the only way I can get them to take the format of the destination cell is by double-clicking then pressing enter. Isn't there any way of forcing this ? -- David M WinXP - Office2003 (Italian) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing cell value update/refresh
Say we want to refresh Z100:
Range("Z100").Calculate or Range("Z100").Replace What:="=", Replacement:="=" -- Gary''s Student - gsnu200768 "#DIV/0" wrote: I'm pretty sure I read the solution to this but after searching for ages can't find it so sorry if this is a repeat... I'm placing values into a worksheet from a userform and the only way I can get them to take the format of the destination cell is by double-clicking then pressing enter. Isn't there any way of forcing this ? -- David M WinXP - Office2003 (Italian) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing cell value update/refresh
I'm not sure how you're doing it, but make sure you format the destination cell
before inserting the value. with activesheet.range("a1") .numberformat = "General" 'or what you want .value = me.combobox1.value 'or whatever end with If this doesn't help, what are you putting into the cell? #DIV/0 wrote: I'm pretty sure I read the solution to this but after searching for ages can't find it so sorry if this is a repeat... I'm placing values into a worksheet from a userform and the only way I can get them to take the format of the destination cell is by double-clicking then pressing enter. Isn't there any way of forcing this ? -- David M WinXP - Office2003 (Italian) -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing cell value update/refresh
Hi
I tried both your suggestions but I'm still not getting results. I'm putting formulae into various cells on the sheet. Part of the formulae is made up of text strings I've picked up during the macro such as ActiveCell.Formula = "=SUM('LISTING'!B1:B" & TheRow & ")" What I see in the cell is #NAME? until I double-click it and press enter. Then the cell realises it contains a formula and does the calculation. Any ideas ? -- David M WinXP - Office2003 (Italian) "Gary''s Student" wrote: Say we want to refresh Z100: Range("Z100").Calculate or Range("Z100").Replace What:="=", Replacement:="=" -- Gary''s Student - gsnu200768 "#DIV/0" wrote: I'm pretty sure I read the solution to this but after searching for ages can't find it so sorry if this is a repeat... I'm placing values into a worksheet from a userform and the only way I can get them to take the format of the destination cell is by double-clicking then pressing enter. Isn't there any way of forcing this ? -- David M WinXP - Office2003 (Italian) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing cell value update/refresh
Hi Dave,
Thanks for your response but I'm afraid the wording in my post makes the problem sound like formatting, but if you look at my reply to Gary's Student you'll see that's not quite it. -- David M WinXP - Office2003 (Italian) "Dave Peterson" wrote: I'm not sure how you're doing it, but make sure you format the destination cell before inserting the value. with activesheet.range("a1") .numberformat = "General" 'or what you want .value = me.combobox1.value 'or whatever end with If this doesn't help, what are you putting into the cell? #DIV/0 wrote: I'm pretty sure I read the solution to this but after searching for ages can't find it so sorry if this is a repeat... I'm placing values into a worksheet from a userform and the only way I can get them to take the format of the destination cell is by double-clicking then pressing enter. Isn't there any way of forcing this ? -- David M WinXP - Office2003 (Italian) -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing cell value update/refresh
Under some circumstances, putting a formula in a cell with code rather than
the Excel UI results in Excel not seeing the result as a formula. Try this first: ActiveCell.Clear ActiveCell.Formula = "=SUM('LISTING'!B1:B" & TheRow & ")" if this does not work, then try: ActiveCell.Formula = "=SUM('LISTING'!B1:B" & TheRow & ")" Application.SendKeys "{F2}" Application.SendKeys "{ENTER}" DoEvents -- Gary''s Student - gsnu200768 "#DIV/0" wrote: Hi I tried both your suggestions but I'm still not getting results. I'm putting formulae into various cells on the sheet. Part of the formulae is made up of text strings I've picked up during the macro such as ActiveCell.Formula = "=SUM('LISTING'!B1:B" & TheRow & ")" What I see in the cell is #NAME? until I double-click it and press enter. Then the cell realises it contains a formula and does the calculation. Any ideas ? -- David M WinXP - Office2003 (Italian) "Gary''s Student" wrote: Say we want to refresh Z100: Range("Z100").Calculate or Range("Z100").Replace What:="=", Replacement:="=" -- Gary''s Student - gsnu200768 "#DIV/0" wrote: I'm pretty sure I read the solution to this but after searching for ages can't find it so sorry if this is a repeat... I'm placing values into a worksheet from a userform and the only way I can get them to take the format of the destination cell is by double-clicking then pressing enter. Isn't there any way of forcing this ? -- David M WinXP - Office2003 (Italian) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing cell value update/refresh
Did you try changing the numberformat to general first?
#DIV/0 wrote: Hi Dave, Thanks for your response but I'm afraid the wording in my post makes the problem sound like formatting, but if you look at my reply to Gary's Student you'll see that's not quite it. -- David M WinXP - Office2003 (Italian) "Dave Peterson" wrote: I'm not sure how you're doing it, but make sure you format the destination cell before inserting the value. with activesheet.range("a1") .numberformat = "General" 'or what you want .value = me.combobox1.value 'or whatever end with If this doesn't help, what are you putting into the cell? #DIV/0 wrote: I'm pretty sure I read the solution to this but after searching for ages can't find it so sorry if this is a repeat... I'm placing values into a worksheet from a userform and the only way I can get them to take the format of the destination cell is by double-clicking then pressing enter. Isn't there any way of forcing this ? -- David M WinXP - Office2003 (Italian) -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Forcing cell with user function to update on workbook open? | Excel Programming | |||
forcing refresh links | Excel Discussion (Misc queries) | |||
forcing excel to update the Cell Link when copying Combo Boxes | Excel Worksheet Functions | |||
Date in cell to update upon Pivot Table refresh | Excel Programming | |||
Forcing users to update spreadsheets | Excel Discussion (Misc queries) |