BeforeSave, Screenupdating = False, Why is it not working

[Claus]

Hello

I want to set Screenupdating = False before saving, because i've got 12
sheets with aprox. 1000 rows, and i see the sheet changing while saving.

I did like this and it dosent work:

Private Sub Workbook_BeforeSave()
Application.ScreenUpdating = False
End Sub


How do i turn on Screenupdating after saving ?


Kind Regards

Claus

[Dave Peterson]

Application.screenupdating will turn back to true when the routine ends.

But you must have other code running--I've never seen saving a workbook change
active worksheets.

Claus wrote:
>
> Hello
>
> I want to set Screenupdating = False before saving, because i've got 12
> sheets with aprox. 1000 rows, and i see the sheet changing while saving.
>
> I did like this and it dosent work:
>
> Private Sub Workbook_BeforeSave()
> Application.ScreenUpdating = False
> End Sub
>
> How do i turn on Screenupdating after saving ?
>
> Kind Regards
>
> Claus

--

Dave Peterson

[Claus]

Hi Dave

Thanks for your mail.

I've found out that, when saving, the program is running code belonging to a
ComboBox_Change Sub even though the combo neither have focus nor has
changed. The Sub is also running when copying sheets from one workbook to
another.

I have no code with BeforeSave.

Do you have any suggestions to what migth be the problem.



Claus Persson

"Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
news:42C1F2A9.D70B8D4C@netscapeXSPAM.com...
> Application.screenupdating will turn back to true when the routine ends.
>
> But you must have other code running--I've never seen saving a workbook
> change
> active worksheets.
>
> Claus wrote:
>>
>> Hello
>>
>> I want to set Screenupdating = False before saving, because i've got 12
>> sheets with aprox. 1000 rows, and i see the sheet changing while saving.
>>
>> I did like this and it dosent work:
>>
>> Private Sub Workbook_BeforeSave()
>> Application.ScreenUpdating = False
>> End Sub
>>
>> How do i turn on Screenupdating after saving ?
>>
>> Kind Regards
>>
>> Claus
>
> --
>
> Dave Peterson

[Dave Peterson]

I think I've read posts that this can happen if the combobox has a linked cell.
Do you have a linked cell?

Can you get rid of it and just populate that cell in code?

Claus wrote:
>
> Hi Dave
>
> Thanks for your mail.
>
> I've found out that, when saving, the program is running code belonging to a
> ComboBox_Change Sub even though the combo neither have focus nor has
> changed. The Sub is also running when copying sheets from one workbook to
> another.
>
> I have no code with BeforeSave.
>
> Do you have any suggestions to what migth be the problem.
>
> Claus Persson
>
> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
> news:42C1F2A9.D70B8D4C@netscapeXSPAM.com...
> > Application.screenupdating will turn back to true when the routine ends.
> >
> > But you must have other code running--I've never seen saving a workbook
> > change
> > active worksheets.
> >
> > Claus wrote:
> >>
> >> Hello
> >>
> >> I want to set Screenupdating = False before saving, because i've got 12
> >> sheets with aprox. 1000 rows, and i see the sheet changing while saving.
> >>
> >> I did like this and it dosent work:
> >>
> >> Private Sub Workbook_BeforeSave()
> >> Application.ScreenUpdating = False
> >> End Sub
> >>
> >> How do i turn on Screenupdating after saving ?
> >>
> >> Kind Regards
> >>
> >> Claus
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Claus]

The Combo has no linked cell, but i have togglebuttons with linked cells.
When the combo is deleted the problem disapears.

LinkedCell is empty
ListFillRange is a Name referring to another sheet range O5:P8

Claus

"Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
news:42C52BBF.EF5265F0@netscapeXSPAM.com...
>I think I've read posts that this can happen if the combobox has a linked
>cell.
> Do you have a linked cell?
>
> Can you get rid of it and just populate that cell in code?
>
> Claus wrote:
>>
>> Hi Dave
>>
>> Thanks for your mail.
>>
>> I've found out that, when saving, the program is running code belonging
>> to a
>> ComboBox_Change Sub even though the combo neither have focus nor has
>> changed. The Sub is also running when copying sheets from one workbook to
>> another.
>>
>> I have no code with BeforeSave.
>>
>> Do you have any suggestions to what migth be the problem.
>>
>> Claus Persson
>>
>> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
>> news:42C1F2A9.D70B8D4C@netscapeXSPAM.com...
>> > Application.screenupdating will turn back to true when the routine
>> > ends.
>> >
>> > But you must have other code running--I've never seen saving a workbook
>> > change
>> > active worksheets.
>> >
>> > Claus wrote:
>> >>
>> >> Hello
>> >>
>> >> I want to set Screenupdating = False before saving, because i've got
>> >> 12
>> >> sheets with aprox. 1000 rows, and i see the sheet changing while
>> >> saving.
>> >>
>> >> I did like this and it dosent work:
>> >>
>> >> Private Sub Workbook_BeforeSave()
>> >> Application.ScreenUpdating = False
>> >> End Sub
>> >>
>> >> How do i turn on Screenupdating after saving ?
>> >>
>> >> Kind Regards
>> >>
>> >> Claus
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

[Dave Peterson]

How about removing the listfillrange and seeing what happens?

Claus wrote:
>
> The Combo has no linked cell, but i have togglebuttons with linked cells.
> When the combo is deleted the problem disapears.
>
> LinkedCell is empty
> ListFillRange is a Name referring to another sheet range O5:P8
>
> Claus
>
> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
> news:42C52BBF.EF5265F0@netscapeXSPAM.com...
> >I think I've read posts that this can happen if the combobox has a linked
> >cell.
> > Do you have a linked cell?
> >
> > Can you get rid of it and just populate that cell in code?
> >
> > Claus wrote:
> >>
> >> Hi Dave
> >>
> >> Thanks for your mail.
> >>
> >> I've found out that, when saving, the program is running code belonging
> >> to a
> >> ComboBox_Change Sub even though the combo neither have focus nor has
> >> changed. The Sub is also running when copying sheets from one workbook to
> >> another.
> >>
> >> I have no code with BeforeSave.
> >>
> >> Do you have any suggestions to what migth be the problem.
> >>
> >> Claus Persson
> >>
> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
> >> news:42C1F2A9.D70B8D4C@netscapeXSPAM.com...
> >> > Application.screenupdating will turn back to true when the routine
> >> > ends.
> >> >
> >> > But you must have other code running--I've never seen saving a workbook
> >> > change
> >> > active worksheets.
> >> >
> >> > Claus wrote:
> >> >>
> >> >> Hello
> >> >>
> >> >> I want to set Screenupdating = False before saving, because i've got
> >> >> 12
> >> >> sheets with aprox. 1000 rows, and i see the sheet changing while
> >> >> saving.
> >> >>
> >> >> I did like this and it dosent work:
> >> >>
> >> >> Private Sub Workbook_BeforeSave()
> >> >> Application.ScreenUpdating = False
> >> >> End Sub
> >> >>
> >> >> How do i turn on Screenupdating after saving ?
> >> >>
> >> >> Kind Regards
> >> >>
> >> >> Claus
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Claus]

It seems to solve the problem, but how do I fill in the content of the combo
then ?

Claus
"Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
news:42C56E0E.2645EC57@netscapeXSPAM.com...
> How about removing the listfillrange and seeing what happens?
>
> Claus wrote:
>>
>> The Combo has no linked cell, but i have togglebuttons with linked cells.
>> When the combo is deleted the problem disapears.
>>
>> LinkedCell is empty
>> ListFillRange is a Name referring to another sheet range O5:P8
>>
>> Claus
>>
>> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
>> news:42C52BBF.EF5265F0@netscapeXSPAM.com...
>> >I think I've read posts that this can happen if the combobox has a
>> >linked
>> >cell.
>> > Do you have a linked cell?
>> >
>> > Can you get rid of it and just populate that cell in code?
>> >
>> > Claus wrote:
>> >>
>> >> Hi Dave
>> >>
>> >> Thanks for your mail.
>> >>
>> >> I've found out that, when saving, the program is running code
>> >> belonging
>> >> to a
>> >> ComboBox_Change Sub even though the combo neither have focus nor has
>> >> changed. The Sub is also running when copying sheets from one workbook
>> >> to
>> >> another.
>> >>
>> >> I have no code with BeforeSave.
>> >>
>> >> Do you have any suggestions to what migth be the problem.
>> >>
>> >> Claus Persson
>> >>
>> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
>> >> news:42C1F2A9.D70B8D4C@netscapeXSPAM.com...
>> >> > Application.screenupdating will turn back to true when the routine
>> >> > ends.
>> >> >
>> >> > But you must have other code running--I've never seen saving a
>> >> > workbook
>> >> > change
>> >> > active worksheets.
>> >> >
>> >> > Claus wrote:
>> >> >>
>> >> >> Hello
>> >> >>
>> >> >> I want to set Screenupdating = False before saving, because i've
>> >> >> got
>> >> >> 12
>> >> >> sheets with aprox. 1000 rows, and i see the sheet changing while
>> >> >> saving.
>> >> >>
>> >> >> I did like this and it dosent work:
>> >> >>
>> >> >> Private Sub Workbook_BeforeSave()
>> >> >> Application.ScreenUpdating = False
>> >> >> End Sub
>> >> >>
>> >> >> How do i turn on Screenupdating after saving ?
>> >> >>
>> >> >> Kind Regards
>> >> >>
>> >> >> Claus
>> >> >
>> >> > --
>> >> >
>> >> > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

[Dave Peterson]

Maybe you could use some code--when the workbook is opened?

Option Explicit
Private Sub Workbook_Open()

Dim myRng As Range
Dim myCell As Range

With Sheet1
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
With .ComboBox1
For Each myCell In myRng.Cells
.AddItem myCell.Text
Next myCell
End With
End With

End Sub



Claus wrote:
>
> It seems to solve the problem, but how do I fill in the content of the combo
> then ?
>
> Claus
> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
> news:42C56E0E.2645EC57@netscapeXSPAM.com...
> > How about removing the listfillrange and seeing what happens?
> >
> > Claus wrote:
> >>
> >> The Combo has no linked cell, but i have togglebuttons with linked cells.
> >> When the combo is deleted the problem disapears.
> >>
> >> LinkedCell is empty
> >> ListFillRange is a Name referring to another sheet range O5:P8
> >>
> >> Claus
> >>
> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
> >> news:42C52BBF.EF5265F0@netscapeXSPAM.com...
> >> >I think I've read posts that this can happen if the combobox has a
> >> >linked
> >> >cell.
> >> > Do you have a linked cell?
> >> >
> >> > Can you get rid of it and just populate that cell in code?
> >> >
> >> > Claus wrote:
> >> >>
> >> >> Hi Dave
> >> >>
> >> >> Thanks for your mail.
> >> >>
> >> >> I've found out that, when saving, the program is running code
> >> >> belonging
> >> >> to a
> >> >> ComboBox_Change Sub even though the combo neither have focus nor has
> >> >> changed. The Sub is also running when copying sheets from one workbook
> >> >> to
> >> >> another.
> >> >>
> >> >> I have no code with BeforeSave.
> >> >>
> >> >> Do you have any suggestions to what migth be the problem.
> >> >>
> >> >> Claus Persson
> >> >>
> >> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
> >> >> news:42C1F2A9.D70B8D4C@netscapeXSPAM.com...
> >> >> > Application.screenupdating will turn back to true when the routine
> >> >> > ends.
> >> >> >
> >> >> > But you must have other code running--I've never seen saving a
> >> >> > workbook
> >> >> > change
> >> >> > active worksheets.
> >> >> >
> >> >> > Claus wrote:
> >> >> >>
> >> >> >> Hello
> >> >> >>
> >> >> >> I want to set Screenupdating = False before saving, because i've
> >> >> >> got
> >> >> >> 12
> >> >> >> sheets with aprox. 1000 rows, and i see the sheet changing while
> >> >> >> saving.
> >> >> >>
> >> >> >> I did like this and it dosent work:
> >> >> >>
> >> >> >> Private Sub Workbook_BeforeSave()
> >> >> >> Application.ScreenUpdating = False
> >> >> >> End Sub
> >> >> >>
> >> >> >> How do i turn on Screenupdating after saving ?
> >> >> >>
> >> >> >> Kind Regards
> >> >> >>
> >> >> >> Claus
> >> >> >
> >> >> > --
> >> >> >
> >> >> > Dave Peterson
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Claus]

Thanks Dave

It worked.
Looks like this now:

Option Explicit
Private Sub Workbook_Open()
Dim myRng As Range
Dim myCell As Range

Set myRng = Sheets("Data").Range("P5", "P8")
Sheets("E1").ComboBoxVis.Clear
With Sheets("E1").ComboBoxVis
For Each myCell In myRng.Cells
.AddItem myCell.Text
Next myCell
End With
End Sub


I will add code so that it is done for all my sheets from "E1" to "E12" when
i've made them.

Claus

"Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
news:42C92A42.A98756A4@netscapeXSPAM.com...
> Maybe you could use some code--when the workbook is opened?
>
> Option Explicit
> Private Sub Workbook_Open()
>
> Dim myRng As Range
> Dim myCell As Range
>
> With Sheet1
> Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
> With .ComboBox1
> For Each myCell In myRng.Cells
> .AddItem myCell.Text
> Next myCell
> End With
> End With
>
> End Sub
>
>
>
> Claus wrote:
>>
>> It seems to solve the problem, but how do I fill in the content of the
>> combo
>> then ?
>>
>> Claus
>> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
>> news:42C56E0E.2645EC57@netscapeXSPAM.com...
>> > How about removing the listfillrange and seeing what happens?
>> >
>> > Claus wrote:
>> >>
>> >> The Combo has no linked cell, but i have togglebuttons with linked
>> >> cells.
>> >> When the combo is deleted the problem disapears.
>> >>
>> >> LinkedCell is empty
>> >> ListFillRange is a Name referring to another sheet range O5:P8
>> >>
>> >> Claus
>> >>
>> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
>> >> news:42C52BBF.EF5265F0@netscapeXSPAM.com...
>> >> >I think I've read posts that this can happen if the combobox has a
>> >> >linked
>> >> >cell.
>> >> > Do you have a linked cell?
>> >> >
>> >> > Can you get rid of it and just populate that cell in code?
>> >> >
>> >> > Claus wrote:
>> >> >>
>> >> >> Hi Dave
>> >> >>
>> >> >> Thanks for your mail.
>> >> >>
>> >> >> I've found out that, when saving, the program is running code
>> >> >> belonging
>> >> >> to a
>> >> >> ComboBox_Change Sub even though the combo neither have focus nor
>> >> >> has
>> >> >> changed. The Sub is also running when copying sheets from one
>> >> >> workbook
>> >> >> to
>> >> >> another.
>> >> >>
>> >> >> I have no code with BeforeSave.
>> >> >>
>> >> >> Do you have any suggestions to what migth be the problem.
>> >> >>
>> >> >> Claus Persson
>> >> >>
>> >> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
>> >> >> news:42C1F2A9.D70B8D4C@netscapeXSPAM.com...
>> >> >> > Application.screenupdating will turn back to true when the
>> >> >> > routine
>> >> >> > ends.
>> >> >> >
>> >> >> > But you must have other code running--I've never seen saving a
>> >> >> > workbook
>> >> >> > change
>> >> >> > active worksheets.
>> >> >> >
>> >> >> > Claus wrote:
>> >> >> >>
>> >> >> >> Hello
>> >> >> >>
>> >> >> >> I want to set Screenupdating = False before saving, because i've
>> >> >> >> got
>> >> >> >> 12
>> >> >> >> sheets with aprox. 1000 rows, and i see the sheet changing while
>> >> >> >> saving.
>> >> >> >>
>> >> >> >> I did like this and it dosent work:
>> >> >> >>
>> >> >> >> Private Sub Workbook_BeforeSave()
>> >> >> >> Application.ScreenUpdating = False
>> >> >> >> End Sub
>> >> >> >>
>> >> >> >> How do i turn on Screenupdating after saving ?
>> >> >> >>
>> >> >> >> Kind Regards
>> >> >> >>
>> >> >> >> Claus
>> >> >> >
>> >> >> > --
>> >> >> >
>> >> >> > Dave Peterson
>> >> >
>> >> > --
>> >> >
>> >> > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

[Dave Peterson]

Glad you got it working.

Claus wrote:
>
> Thanks Dave
>
> It worked.
> Looks like this now:
>
> Option Explicit
> Private Sub Workbook_Open()
> Dim myRng As Range
> Dim myCell As Range
>
> Set myRng = Sheets("Data").Range("P5", "P8")
> Sheets("E1").ComboBoxVis.Clear
> With Sheets("E1").ComboBoxVis
> For Each myCell In myRng.Cells
> .AddItem myCell.Text
> Next myCell
> End With
> End Sub
>
> I will add code so that it is done for all my sheets from "E1" to "E12" when
> i've made them.
>
> Claus
>
> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
> news:42C92A42.A98756A4@netscapeXSPAM.com...
> > Maybe you could use some code--when the workbook is opened?
> >
> > Option Explicit
> > Private Sub Workbook_Open()
> >
> > Dim myRng As Range
> > Dim myCell As Range
> >
> > With Sheet1
> > Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
> > With .ComboBox1
> > For Each myCell In myRng.Cells
> > .AddItem myCell.Text
> > Next myCell
> > End With
> > End With
> >
> > End Sub
> >
> >
> >
> > Claus wrote:
> >>
> >> It seems to solve the problem, but how do I fill in the content of the
> >> combo
> >> then ?
> >>
> >> Claus
> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
> >> news:42C56E0E.2645EC57@netscapeXSPAM.com...
> >> > How about removing the listfillrange and seeing what happens?
> >> >
> >> > Claus wrote:
> >> >>
> >> >> The Combo has no linked cell, but i have togglebuttons with linked
> >> >> cells.
> >> >> When the combo is deleted the problem disapears.
> >> >>
> >> >> LinkedCell is empty
> >> >> ListFillRange is a Name referring to another sheet range O5:P8
> >> >>
> >> >> Claus
> >> >>
> >> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
> >> >> news:42C52BBF.EF5265F0@netscapeXSPAM.com...
> >> >> >I think I've read posts that this can happen if the combobox has a
> >> >> >linked
> >> >> >cell.
> >> >> > Do you have a linked cell?
> >> >> >
> >> >> > Can you get rid of it and just populate that cell in code?
> >> >> >
> >> >> > Claus wrote:
> >> >> >>
> >> >> >> Hi Dave
> >> >> >>
> >> >> >> Thanks for your mail.
> >> >> >>
> >> >> >> I've found out that, when saving, the program is running code
> >> >> >> belonging
> >> >> >> to a
> >> >> >> ComboBox_Change Sub even though the combo neither have focus nor
> >> >> >> has
> >> >> >> changed. The Sub is also running when copying sheets from one
> >> >> >> workbook
> >> >> >> to
> >> >> >> another.
> >> >> >>
> >> >> >> I have no code with BeforeSave.
> >> >> >>
> >> >> >> Do you have any suggestions to what migth be the problem.
> >> >> >>
> >> >> >> Claus Persson
> >> >> >>
> >> >> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i en meddelelse
> >> >> >> news:42C1F2A9.D70B8D4C@netscapeXSPAM.com...
> >> >> >> > Application.screenupdating will turn back to true when the
> >> >> >> > routine
> >> >> >> > ends.
> >> >> >> >
> >> >> >> > But you must have other code running--I've never seen saving a
> >> >> >> > workbook
> >> >> >> > change
> >> >> >> > active worksheets.
> >> >> >> >
> >> >> >> > Claus wrote:
> >> >> >> >>
> >> >> >> >> Hello
> >> >> >> >>
> >> >> >> >> I want to set Screenupdating = False before saving, because i've
> >> >> >> >> got
> >> >> >> >> 12
> >> >> >> >> sheets with aprox. 1000 rows, and i see the sheet changing while
> >> >> >> >> saving.
> >> >> >> >>
> >> >> >> >> I did like this and it dosent work:
> >> >> >> >>
> >> >> >> >> Private Sub Workbook_BeforeSave()
> >> >> >> >> Application.ScreenUpdating = False
> >> >> >> >> End Sub
> >> >> >> >>
> >> >> >> >> How do i turn on Screenupdating after saving ?
> >> >> >> >>
> >> >> >> >> Kind Regards
> >> >> >> >>
> >> >> >> >> Claus
> >> >> >> >
> >> >> >> > --
> >> >> >> >
> >> >> >> > Dave Peterson
> >> >> >
> >> >> > --
> >> >> >
> >> >> > Dave Peterson
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson