Combo Box ListFillRange causing save prompt
 

I've seen a few posts of people with a similar problem, but I haven't
seen any solutions.

I have an Excel Workbook (Excel 2002) that contains a combo box. When
the ListFillRange property is populated I am prompted to save changes
every time I close the workbook - even when no changes have been made.
The ListFillRange values are static and contain NO formulas. I need to
prevent the save prompt from showing when no changes have been made.
Any suggestions?

One post mentioned setting the ListFillRange to blank unless the combo
box was activated. Does anyone know how to do that?

Thanks.

Combo Box ListFillRange causing save prompt
 

Maybe you could just drop the listfillrange and populate the list in code?

Maybe 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

wrote:

I've seen a few posts of people with a similar problem, but I haven't
seen any solutions.

I have an Excel Workbook (Excel 2002) that contains a combo box. When
the ListFillRange property is populated I am prompted to save changes
every time I close the workbook - even when no changes have been made.
The ListFillRange values are static and contain NO formulas. I need to
prevent the save prompt from showing when no changes have been made.
Any suggestions?

One post mentioned setting the ListFillRange to blank unless the combo
box was activated. Does anyone know how to do that?

Thanks.

--

Dave Peterson

Combo Box ListFillRange causing save prompt
 

I've added that code instead of using the ListFillRange, but I'm still
getting the same save prompts. Is there a way to clear the values
before closing the workbook?

Dave Peterson wrote:
Maybe you could just drop the listfillrange and populate the list in code?

Maybe 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

wrote:

I've seen a few posts of people with a similar problem, but I haven't
seen any solutions.

I have an Excel Workbook (Excel 2002) that contains a combo box. When
the ListFillRange property is populated I am prompted to save changes
every time I close the workbook - even when no changes have been made.
The ListFillRange values are static and contain NO formulas. I need to
prevent the save prompt from showing when no changes have been made.
Any suggestions?

One post mentioned setting the ListFillRange to blank unless the combo
box was activated. Does anyone know how to do that?

Thanks.

--

Dave Peterson

Combo Box ListFillRange causing save prompt
 

Oooh. If you use the auto_open to fill the listfillrange, then you're changing
the workbook (Doh!).

Maybe you could just add one line to the end of the auto_open() routine.

thisworkbook.saved = true

(It's telling excel to consider the workbook saved at that instant.)

If you make changes after that, the prompt will come back.

wrote:

I've added that code instead of using the ListFillRange, but I'm still
getting the same save prompts. Is there a way to clear the values
before closing the workbook?

Dave Peterson wrote:
Maybe you could just drop the listfillrange and populate the list in code?

Maybe 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

wrote:

I've seen a few posts of people with a similar problem, but I haven't
seen any solutions.

I have an Excel Workbook (Excel 2002) that contains a combo box. When
the ListFillRange property is populated I am prompted to save changes
every time I close the workbook - even when no changes have been made.
The ListFillRange values are static and contain NO formulas. I need to
prevent the save prompt from showing when no changes have been made.
Any suggestions?

One post mentioned setting the ListFillRange to blank unless the combo
box was activated. Does anyone know how to do that?

Thanks.

--

Dave Peterson

--

Dave Peterson

Combo Box ListFillRange causing save prompt
 

I've already have that line, but it still prompts me to save changes
when I haven't changed anything.

Dave Peterson wrote:
Oooh. If you use the auto_open to fill the listfillrange, then you're changing
the workbook (Doh!).

Maybe you could just add one line to the end of the auto_open() routine.

thisworkbook.saved = true

(It's telling excel to consider the workbook saved at that instant.)

If you make changes after that, the prompt will come back.

wrote:

I've added that code instead of using the ListFillRange, but I'm still
getting the same save prompts. Is there a way to clear the values
before closing the workbook?

Dave Peterson wrote:
Maybe you could just drop the listfillrange and populate the list in code?

Maybe 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

wrote:

I've seen a few posts of people with a similar problem, but I haven't
seen any solutions.

I have an Excel Workbook (Excel 2002) that contains a combo box. When
the ListFillRange property is populated I am prompted to save changes
every time I close the workbook - even when no changes have been made.
The ListFillRange values are static and contain NO formulas. I need to
prevent the save prompt from showing when no changes have been made.
Any suggestions?

One post mentioned setting the ListFillRange to blank unless the combo
box was activated. Does anyone know how to do that?

Thanks.

--

Dave Peterson

--

Dave Peterson

Combo Box ListFillRange causing save prompt
 

Do you have a linkedcell for that combobox?

If you do, try removing it and seeing if that helps.

wrote:

I've already have that line, but it still prompts me to save changes
when I haven't changed anything.

Dave Peterson wrote:
Oooh. If you use the auto_open to fill the listfillrange, then you're changing
the workbook (Doh!).

Maybe you could just add one line to the end of the auto_open() routine.

thisworkbook.saved = true

(It's telling excel to consider the workbook saved at that instant.)

If you make changes after that, the prompt will come back.

wrote:

I've added that code instead of using the ListFillRange, but I'm still
getting the same save prompts. Is there a way to clear the values
before closing the workbook?

Dave Peterson wrote:
Maybe you could just drop the listfillrange and populate the list in code?

Maybe 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

wrote:

I've seen a few posts of people with a similar problem, but I haven't
seen any solutions.

I have an Excel Workbook (Excel 2002) that contains a combo box. When
the ListFillRange property is populated I am prompted to save changes
every time I close the workbook - even when no changes have been made.
The ListFillRange values are static and contain NO formulas. I need to
prevent the save prompt from showing when no changes have been made.
Any suggestions?

One post mentioned setting the ListFillRange to blank unless the combo
box was activated. Does anyone know how to do that?

Thanks.

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson