Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2008

Tip: Looking for answers? Try searching our database.

This is killing me

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Michael - 12 Mar 2008 20:35 GMT
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.

Signature

If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

PCLIVE - 12 Mar 2008 20:40 GMT
Maybe Events are disabled.  Try this at the top of your code.

Application.EnableEvents = True

Regards,
Paul

> All I want to do is to make sure every time there is a change in the
> combobox
[quoted text clipped - 43 lines]
> I have ensured that in my options, I have set my calculation to automatic
> Calculation.
Dave Peterson - 12 Mar 2008 20:49 GMT
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.

> 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
[quoted text clipped - 46 lines]
>
> Michael Arch.

Signature

Dave Peterson

Michael - 12 Mar 2008 21:05 GMT
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.
Signature

If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

> Untested.
>
[quoted text clipped - 59 lines]
> >
> > Michael Arch.
Ed from AZ - 12 Mar 2008 21:35 GMT
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
[quoted text clipped - 3 lines]
> > >         alreadyupdate = True *****
> > >         Application.Volatile

[SNIP]

> > > Private Sub ComboBox1_Change()
> > >        If alreadyupdated ******* Then
[quoted text clipped - 3 lines]
> > > End If
> > >     alreadyupdate = True *******

Ed
Michael - 12 Mar 2008 22:07 GMT
Thanks but the typo on the update word is not the issue it is just a typo.
They both read update in my code.
Signature

If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

> There's a minor typo - if this is a direct copy / paste from your
> module, it might be giving you problems:
[quoted text clipped - 20 lines]
>
> Ed
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.