Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is killing me
All I want to do is to make sure every time there is a change in the combobox
all cells are updated, but it doesn't so I tried forcing it byreplacing and equal sign by an equal sign something like this: Sub UpdateWkbk() Dim sht As Worksheet Dim dashsht As Worksheet Application.ScreenUpdating = False Set dashsht = Worksheets("Dashboard") Set sht = Worksheets("Data") sht.Select alreadyupdate = True For i = 1 To 10 sht.Range("J8:U40").Replace What:="=", Replacement:="=", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False alreadyupdate = True Application.Volatile Application.SendKeys ("%XR") ' This is to execute a refresh of a UDF because is also not happening automatically. Next i dashsht.Select Application.ScreenUpdating = True End Sub However, this triggers the Combobox So I figure I work around it like this: Private Sub ComboBox1_Change() If alreadyupdated Then Exit Sub Else UpdateWkbk End If alreadyupdate = True End Sub Nothing is working I have ensured that in my options, I have set my calculation to automatic Calculation. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is killing me
Maybe Events are disabled. Try this at the top of your code.
Application.EnableEvents = True Regards, Paul -- "Michael" wrote in message ... All I want to do is to make sure every time there is a change in the combobox all cells are updated, but it doesn't so I tried forcing it byreplacing and equal sign by an equal sign something like this: Sub UpdateWkbk() Dim sht As Worksheet Dim dashsht As Worksheet Application.ScreenUpdating = False Set dashsht = Worksheets("Dashboard") Set sht = Worksheets("Data") sht.Select alreadyupdate = True For i = 1 To 10 sht.Range("J8:U40").Replace What:="=", Replacement:="=", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False alreadyupdate = True Application.Volatile Application.SendKeys ("%XR") ' This is to execute a refresh of a UDF because is also not happening automatically. Next i dashsht.Select Application.ScreenUpdating = True End Sub However, this triggers the Combobox So I figure I work around it like this: Private Sub ComboBox1_Change() If alreadyupdated Then Exit Sub Else UpdateWkbk End If alreadyupdate = True End Sub Nothing is working I have ensured that in my options, I have set my calculation to automatic Calculation. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is killing me
Untested.
I would have thought that: Private Sub ComboBox1_Change() application.calculate 'or depending on your version of excel application.calculateFull application.CalculateFullRebuild End sub Would have worked. Michael wrote: All I want to do is to make sure every time there is a change in the combobox all cells are updated, but it doesn't so I tried forcing it byreplacing and equal sign by an equal sign something like this: Sub UpdateWkbk() Dim sht As Worksheet Dim dashsht As Worksheet Application.ScreenUpdating = False Set dashsht = Worksheets("Dashboard") Set sht = Worksheets("Data") sht.Select alreadyupdate = True For i = 1 To 10 sht.Range("J8:U40").Replace What:="=", Replacement:="=", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False alreadyupdate = True Application.Volatile Application.SendKeys ("%XR") ' This is to execute a refresh of a UDF because is also not happening automatically. Next i dashsht.Select Application.ScreenUpdating = True End Sub However, this triggers the Combobox So I figure I work around it like this: Private Sub ComboBox1_Change() If alreadyupdated Then Exit Sub Else UpdateWkbk End If alreadyupdate = True End Sub Nothing is working I have ensured that in my options, I have set my calculation to automatic Calculation. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is killing me
I just added both statements and still won't update, I have Excel 2003 SP3.
However, If I do it step by step it works, but then it defeats the purpose of having a sub. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Dave Peterson" wrote: Untested. I would have thought that: Private Sub ComboBox1_Change() application.calculate 'or depending on your version of excel application.calculateFull application.CalculateFullRebuild End sub Would have worked. Michael wrote: All I want to do is to make sure every time there is a change in the combobox all cells are updated, but it doesn't so I tried forcing it byreplacing and equal sign by an equal sign something like this: Sub UpdateWkbk() Dim sht As Worksheet Dim dashsht As Worksheet Application.ScreenUpdating = False Set dashsht = Worksheets("Dashboard") Set sht = Worksheets("Data") sht.Select alreadyupdate = True For i = 1 To 10 sht.Range("J8:U40").Replace What:="=", Replacement:="=", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False alreadyupdate = True Application.Volatile Application.SendKeys ("%XR") ' This is to execute a refresh of a UDF because is also not happening automatically. Next i dashsht.Select Application.ScreenUpdating = True End Sub However, this triggers the Combobox So I figure I work around it like this: Private Sub ComboBox1_Change() If alreadyupdated Then Exit Sub Else UpdateWkbk End If alreadyupdate = True End Sub Nothing is working I have ensured that in my options, I have set my calculation to automatic Calculation. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is killing me
There's a minor typo - if this is a direct copy / paste from your
module, it might be giving you problems: Sub UpdateWkbk() [SNIP] alreadyupdate = True ***** For i = 1 To 10 sht.Range("J8:U40").Replace What:="=", Replacement:="=", LookAt:=xlPart, _ * * * * SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ * * * * ReplaceFormat:=False * * * * alreadyupdate = True ***** * * * * Application.Volatile [SNIP] Private Sub ComboBox1_Change() * * * *If alreadyupdated ******* Then * * * Exit Sub Else * * UpdateWkbk End If * * alreadyupdate = True ******* Ed |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is killing me
Thanks but the typo on the update word is not the issue it is just a typo.
They both read update in my code. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Ed from AZ" wrote: There's a minor typo - if this is a direct copy / paste from your module, it might be giving you problems: Sub UpdateWkbk() [SNIP] alreadyupdate = True ***** For i = 1 To 10 sht.Range("J8:U40").Replace What:="=", Replacement:="=", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False alreadyupdate = True ***** Application.Volatile [SNIP] Private Sub ComboBox1_Change() If alreadyupdated ******* Then Exit Sub Else UpdateWkbk End If alreadyupdate = True ******* Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Syntax or Something is KILLING Me | Excel Programming | |||
killing all procedures? | Excel Programming | |||
help me out with this - it's killing me.. | Excel Programming | |||
killing a sub from within an if statement | Excel Programming | |||
Killing Excel :) | Excel Programming |