Hi
I want a user to select a year 2001 to 2010, and then based on this
selection choose a (Friday) weekend date. Other calculations are dependant
upon the results from this last selection.

I can do this without programming using data validation and the indirect
function. However I wanted to try to do it using combo boxes.

In the past I have only used the combo box from the forms toolbar and the
input range does not recognise the INDIRECT function, or at least does not
behave in the same way as data validation.

When using the combo box from the control toolbox toolbar the indirect
function does not appear to work when =INDIRECT is entered in the
ListFillRange box in properties.

I searched the discussion groups for a solution and I was directed to
www.xldynamic.com/source/xld.Dropdowns.html from multiple postings.

I copied / imported all of the code and modified it to meet my requirements
re worksheet names (I replaced €œdata€� with €œWeekEndingDates€�) in the module
mComboMaintain and elsewhere as required.

When I open the workbook, after a while an error message appears

Compile error, variable not defined.

When debugging €œcombo€� is highlighted in blue in the line
€œWith combo.cboPrimary€�

The complete code is given below. Can anyone indicate where I need to look?

Regards
Phil Smith

Option Explicit

'---------------------------------------------------------------------
Public Function fzPopulatList1()
'---------------------------------------------------------------------
Dim i As Long

Application.EnableEvents = False
'On Error GoTo pl1_exit

With combo.cboPrimary
.Clear
For i = 2 To Range(kList1Hnd).Count + 1
.AddItem WeekEndingDates.Cells(1, i).Value
Next i

Application.EnableEvents = True
.ListIndex = 0
End With

pl1_exit:
Application.EnableEvents = True

End Function

Philip,

That example is assuming that the worksheet that the combobox is on has a
codename of combo. Either change the codename to combo, or the code to the
worksheet codename, and it should be okay.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Philip J Smith" wrote in message
...
Hi
I want a user to select a year 2001 to 2010, and then based on this
selection choose a (Friday) weekend date. Other calculations are
dependant
upon the results from this last selection.

I can do this without programming using data validation and the indirect
function. However I wanted to try to do it using combo boxes.

In the past I have only used the combo box from the forms toolbar and the
input range does not recognise the INDIRECT function, or at least does not
behave in the same way as data validation.

When using the combo box from the control toolbox toolbar the indirect
function does not appear to work when =INDIRECT is entered in the
ListFillRange box in properties.

I searched the discussion groups for a solution and I was directed to
www.xldynamic.com/source/xld.Dropdowns.html from multiple postings.

I copied / imported all of the code and modified it to meet my
requirements
re worksheet names (I replaced "data" with "WeekEndingDates") in the
module
mComboMaintain and elsewhere as required.

When I open the workbook, after a while an error message appears

Compile error, variable not defined.

When debugging "combo" is highlighted in blue in the line
"With combo.cboPrimary"

The complete code is given below. Can anyone indicate where I need to
look?

Regards
Phil Smith

Option Explicit

'---------------------------------------------------------------------
Public Function fzPopulatList1()
'---------------------------------------------------------------------
Dim i As Long

Application.EnableEvents = False
'On Error GoTo pl1_exit

With combo.cboPrimary
.Clear
For i = 2 To Range(kList1Hnd).Count + 1
.AddItem WeekEndingDates.Cells(1, i).Value
Next i

Application.EnableEvents = True
.ListIndex = 0
End With

pl1_exit:
Application.EnableEvents = True

End Function

Absolutely Brilliant!! I knew I was being thick!

Thanks for the prompt response!

Regards

Phil


"Bob Phillips" wrote:

Philip,

That example is assuming that the worksheet that the combobox is on has a
codename of combo. Either change the codename to combo, or the code to the
worksheet codename, and it should be okay.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Philip J Smith" wrote in message
...
Hi
I want a user to select a year 2001 to 2010, and then based on this
selection choose a (Friday) weekend date. Other calculations are
dependant
upon the results from this last selection.

I can do this without programming using data validation and the indirect
function. However I wanted to try to do it using combo boxes.

In the past I have only used the combo box from the forms toolbar and the
input range does not recognise the INDIRECT function, or at least does not
behave in the same way as data validation.

When using the combo box from the control toolbox toolbar the indirect
function does not appear to work when =INDIRECT is entered in the
ListFillRange box in properties.

I searched the discussion groups for a solution and I was directed to
www.xldynamic.com/source/xld.Dropdowns.html from multiple postings.

I copied / imported all of the code and modified it to meet my
requirements
re worksheet names (I replaced "data" with "WeekEndingDates") in the
module
mComboMaintain and elsewhere as required.

When I open the workbook, after a while an error message appears

Compile error, variable not defined.

When debugging "combo" is highlighted in blue in the line
"With combo.cboPrimary"

The complete code is given below. Can anyone indicate where I need to
look?

Regards
Phil Smith

Option Explicit

'---------------------------------------------------------------------
Public Function fzPopulatList1()
'---------------------------------------------------------------------
Dim i As Long

Application.EnableEvents = False
'On Error GoTo pl1_exit

With combo.cboPrimary
.Clear
For i = 2 To Range(kList1Hnd).Count + 1
.AddItem WeekEndingDates.Cells(1, i).Value
Next i

Application.EnableEvents = True
.ListIndex = 0
End With

pl1_exit:
Application.EnableEvents = True

End Function

Hi Bob.

Thanks for your help last week.

If you can spare a little more time, I have one further question.

I have been trying to amend the code so that the selections are not reset
every time the workbook opens, that is the last selection is to be retained
until the user changes it.

Please, could you indicate what needs to be changed to achieve this
objective? My attempts have been described below.

So as not to lose code during testing I have tried to "text out" by
inserting a " ' " before code in the following cases.

..Clear and .ListIndex =0 in both fzPopulateList1() and fzPopulateList2().

I thought that some combination of these would stop the reset.

The With statements in pzLoadList2Lists()

I think that the last one stops the updating of the ranges on opening, but
that's fine as the lists are of fixed length and update according to formulae.

Regards

Phil


"Bob Phillips" wrote:

Philip,

That example is assuming that the worksheet that the combobox is on has a
codename of combo. Either change the codename to combo, or the code to the
worksheet codename, and it should be okay.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Philip J Smith" wrote in message
...
Hi
I want a user to select a year 2001 to 2010, and then based on this
selection choose a (Friday) weekend date. Other calculations are
dependant
upon the results from this last selection.

I can do this without programming using data validation and the indirect
function. However I wanted to try to do it using combo boxes.

In the past I have only used the combo box from the forms toolbar and the
input range does not recognise the INDIRECT function, or at least does not
behave in the same way as data validation.

When using the combo box from the control toolbox toolbar the indirect
function does not appear to work when =INDIRECT is entered in the
ListFillRange box in properties.

I searched the discussion groups for a solution and I was directed to
www.xldynamic.com/source/xld.Dropdowns.html from multiple postings.

I copied / imported all of the code and modified it to meet my
requirements
re worksheet names (I replaced "data" with "WeekEndingDates") in the
module
mComboMaintain and elsewhere as required.

When I open the workbook, after a while an error message appears

Compile error, variable not defined.

When debugging "combo" is highlighted in blue in the line
"With combo.cboPrimary"

The complete code is given below. Can anyone indicate where I need to
look?

Regards
Phil Smith

Option Explicit

'---------------------------------------------------------------------
Public Function fzPopulatList1()
'---------------------------------------------------------------------
Dim i As Long

Application.EnableEvents = False
'On Error GoTo pl1_exit

With combo.cboPrimary
.Clear
For i = 2 To Range(kList1Hnd).Count + 1
.AddItem WeekEndingDates.Cells(1, i).Value
Next i

Application.EnableEvents = True
.ListIndex = 0
End With

pl1_exit:
Application.EnableEvents = True

End Function

Phil,

It is not quite that simple I am afraid, the thing just wasn't designed that
way. The problem in changing the populate routines is that they are used
whenever a primary selection is made, so it is dangerous.

To achieve what you want, I would save the values on closedown, and restore
them at next open. To do this I would save the values in workbook defined
names on workbook close, and read them on workbook open. No need to touch
the populate routines

Here is a (new) Workbook_BeforClose procedure, and an amended Workbook_Open
procedure that does just that. Just replace all code in ThisWorkbook with
this code


Option Explicit

'---------------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'---------------------------------------------------------------------
ThisWorkbook.Names.Add Name:="__List1", _
RefersTo:=combo.cboPrimary.Value
ThisWorkbook.Names.Add Name:="__ListIndex", _
RefersTo:=combo.cboPrimary.ListIndex + 1
ThisWorkbook.Names.Add Name:="__List2", _
RefersTo:=combo.cboSecondary.Value
End Sub

'---------------------------------------------------------------------
Private Sub Workbook_Open()
'---------------------------------------------------------------------
Dim cell As Range
Dim cboVal As String
Dim cboIdx As Long

frmxldSplash.Show

pzLoadList2Lists

Application.DisplayAlerts = False

kList1 = data.Range("A1").Value
klist2 = data.Range("A2").Value

'this populates the Data Validation lists
Set cell = dv.Range(kList1)
fzCreateValidationList1 cell
fzCreateValidationList2 cell.Offset(1, 0), 1, cell

'this populates the combo boxes
fzPopulatList1
cboIdx = 1
On Error Resume Next
cboVal = Application.Evaluate(ThisWorkbook.Names _
("__List1").RefersTo)
cboIdx = Application.Evaluate(ThisWorkbook.Names _
("__ListIndex").RefersTo)
On Error GoTo 0
'if we have saved the value of combo last time, restore it
If cboVal < "" Then
combo.cboPrimary = cboVal
End If
fzPopulatList2 cboIdx
On Error Resume Next
cboVal = Application.Evaluate(ThisWorkbook.Names _
("__List2").RefersTo)
On Error GoTo 0
If cboVal < "" Then
combo.cboSecondary = cboVal
End If

Application.DisplayAlerts = True

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Philip J Smith" wrote in message
...
Hi Bob.

Thanks for your help last week.

If you can spare a little more time, I have one further question.

I have been trying to amend the code so that the selections are not reset
every time the workbook opens, that is the last selection is to be
retained
until the user changes it.

Please, could you indicate what needs to be changed to achieve this
objective? My attempts have been described below.

So as not to lose code during testing I have tried to "text out" by
inserting a " ' " before code in the following cases.

.Clear and .ListIndex =0 in both fzPopulateList1() and fzPopulateList2().

I thought that some combination of these would stop the reset.

The With statements in pzLoadList2Lists()

I think that the last one stops the updating of the ranges on opening, but
that's fine as the lists are of fixed length and update according to
formulae.

Regards

Phil


"Bob Phillips" wrote:

Philip,

That example is assuming that the worksheet that the combobox is on has a
codename of combo. Either change the codename to combo, or the code to
the
worksheet codename, and it should be okay.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Philip J Smith" wrote in
message
...
Hi
I want a user to select a year 2001 to 2010, and then based on this
selection choose a (Friday) weekend date. Other calculations are
dependant
upon the results from this last selection.

I can do this without programming using data validation and the
indirect
function. However I wanted to try to do it using combo boxes.

In the past I have only used the combo box from the forms toolbar and
the
input range does not recognise the INDIRECT function, or at least does
not
behave in the same way as data validation.

When using the combo box from the control toolbox toolbar the indirect
function does not appear to work when =INDIRECT is entered in the
ListFillRange box in properties.

I searched the discussion groups for a solution and I was directed to
www.xldynamic.com/source/xld.Dropdowns.html from multiple postings.

I copied / imported all of the code and modified it to meet my
requirements
re worksheet names (I replaced "data" with "WeekEndingDates") in the
module
mComboMaintain and elsewhere as required.

When I open the workbook, after a while an error message appears

Compile error, variable not defined.

When debugging "combo" is highlighted in blue in the line
"With combo.cboPrimary"

The complete code is given below. Can anyone indicate where I need to
look?

Regards
Phil Smith

Option Explicit

'---------------------------------------------------------------------
Public Function fzPopulatList1()
'---------------------------------------------------------------------
Dim i As Long

Application.EnableEvents = False
'On Error GoTo pl1_exit

With combo.cboPrimary
.Clear
For i = 2 To Range(kList1Hnd).Count + 1
.AddItem WeekEndingDates.Cells(1, i).Value
Next i

Application.EnableEvents = True
.ListIndex = 0
End With

pl1_exit:
Application.EnableEvents = True

End Function

Many Thanks Bob.

The selections in cboSecondary are dates in the format dd-mm-yyy. On
opening the selection is displayed as a text of the date serial number.

I think that I need to insert a formatting line just below
RefersTo:Combo.cboSecondary.Value
which looks something like
cboSecondary.value=Format(cboSecondary.value,"dd/mm/yyyy")

But I can't get it to work, what am I missing, or is this the wrong place to
achieve what I am attempting to do.

Regards
Phil

"Bob Phillips" wrote:

Phil,

It is not quite that simple I am afraid, the thing just wasn't designed that
way. The problem in changing the populate routines is that they are used
whenever a primary selection is made, so it is dangerous.

To achieve what you want, I would save the values on closedown, and restore
them at next open. To do this I would save the values in workbook defined
names on workbook close, and read them on workbook open. No need to touch
the populate routines

Here is a (new) Workbook_BeforClose procedure, and an amended Workbook_Open
procedure that does just that. Just replace all code in ThisWorkbook with
this code


Option Explicit

'---------------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'---------------------------------------------------------------------
ThisWorkbook.Names.Add Name:="__List1", _
RefersTo:=combo.cboPrimary.Value
ThisWorkbook.Names.Add Name:="__ListIndex", _
RefersTo:=combo.cboPrimary.ListIndex + 1
ThisWorkbook.Names.Add Name:="__List2", _
RefersTo:=combo.cboSecondary.Value
End Sub

'---------------------------------------------------------------------
Private Sub Workbook_Open()
'---------------------------------------------------------------------
Dim cell As Range
Dim cboVal As String
Dim cboIdx As Long

frmxldSplash.Show

pzLoadList2Lists

Application.DisplayAlerts = False

kList1 = data.Range("A1").Value
klist2 = data.Range("A2").Value

'this populates the Data Validation lists
Set cell = dv.Range(kList1)
fzCreateValidationList1 cell
fzCreateValidationList2 cell.Offset(1, 0), 1, cell

'this populates the combo boxes
fzPopulatList1
cboIdx = 1
On Error Resume Next
cboVal = Application.Evaluate(ThisWorkbook.Names _
("__List1").RefersTo)
cboIdx = Application.Evaluate(ThisWorkbook.Names _
("__ListIndex").RefersTo)
On Error GoTo 0
'if we have saved the value of combo last time, restore it
If cboVal < "" Then
combo.cboPrimary = cboVal
End If
fzPopulatList2 cboIdx
On Error Resume Next
cboVal = Application.Evaluate(ThisWorkbook.Names _
("__List2").RefersTo)
On Error GoTo 0
If cboVal < "" Then
combo.cboSecondary = cboVal
End If

Application.DisplayAlerts = True

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Philip J Smith" wrote in message
...
Hi Bob.

Thanks for your help last week.

If you can spare a little more time, I have one further question.

I have been trying to amend the code so that the selections are not reset
every time the workbook opens, that is the last selection is to be
retained
until the user changes it.

Please, could you indicate what needs to be changed to achieve this
objective? My attempts have been described below.

So as not to lose code during testing I have tried to "text out" by
inserting a " ' " before code in the following cases.

.Clear and .ListIndex =0 in both fzPopulateList1() and fzPopulateList2().

I thought that some combination of these would stop the reset.

The With statements in pzLoadList2Lists()

I think that the last one stops the updating of the ranges on opening, but
that's fine as the lists are of fixed length and update according to
formulae.

Regards

Phil


"Bob Phillips" wrote:

Philip,

That example is assuming that the worksheet that the combobox is on has a
codename of combo. Either change the codename to combo, or the code to
the
worksheet codename, and it should be okay.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Philip J Smith" wrote in
message
...
Hi
I want a user to select a year 2001 to 2010, and then based on this
selection choose a (Friday) weekend date. Other calculations are
dependant
upon the results from this last selection.

I can do this without programming using data validation and the
indirect
function. However I wanted to try to do it using combo boxes.

In the past I have only used the combo box from the forms toolbar and
the
input range does not recognise the INDIRECT function, or at least does
not
behave in the same way as data validation.

When using the combo box from the control toolbox toolbar the indirect
function does not appear to work when =INDIRECT is entered in the
ListFillRange box in properties.

I searched the discussion groups for a solution and I was directed to
www.xldynamic.com/source/xld.Dropdowns.html from multiple postings.

I copied / imported all of the code and modified it to meet my
requirements
re worksheet names (I replaced "data" with "WeekEndingDates") in the
module
mComboMaintain and elsewhere as required.

When I open the workbook, after a while an error message appears

Compile error, variable not defined.

When debugging "combo" is highlighted in blue in the line
"With combo.cboPrimary"

The complete code is given below. Can anyone indicate where I need to
look?

Regards
Phil Smith

Option Explicit

'---------------------------------------------------------------------
Public Function fzPopulatList1()
'---------------------------------------------------------------------
Dim i As Long

Application.EnableEvents = False
'On Error GoTo pl1_exit

With combo.cboPrimary
.Clear
For i = 2 To Range(kList1Hnd).Count + 1
.AddItem WeekEndingDates.Cells(1, i).Value
Next i

Application.EnableEvents = True
.ListIndex = 0
End With

pl1_exit:
Application.EnableEvents = True

End Function

Many Thanks Bob.

The selections in cboSecondary are dates in the format dd-mm-yyy. On
opening the selection is displayed as a text of the date serial number.

I think that I need to insert a formatting line just below
RefersTo:Combo.cboSecondary.Value
which looks something like
cboSecondary.value=Format(cboSecondary.value,"dd/mm/yyyy")

But I can't get it to work, what am I missing, or is this the wrong place to
achieve what I am attempting to do.

Regards
Phil

"Bob Phillips" wrote:

Phil,

It is not quite that simple I am afraid, the thing just wasn't designed that
way. The problem in changing the populate routines is that they are used
whenever a primary selection is made, so it is dangerous.

To achieve what you want, I would save the values on closedown, and restore
them at next open. To do this I would save the values in workbook defined
names on workbook close, and read them on workbook open. No need to touch
the populate routines

Here is a (new) Workbook_BeforClose procedure, and an amended Workbook_Open
procedure that does just that. Just replace all code in ThisWorkbook with
this code


Option Explicit

'---------------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'---------------------------------------------------------------------
ThisWorkbook.Names.Add Name:="__List1", _
RefersTo:=combo.cboPrimary.Value
ThisWorkbook.Names.Add Name:="__ListIndex", _
RefersTo:=combo.cboPrimary.ListIndex + 1
ThisWorkbook.Names.Add Name:="__List2", _
RefersTo:=combo.cboSecondary.Value
End Sub

'---------------------------------------------------------------------
Private Sub Workbook_Open()
'---------------------------------------------------------------------
Dim cell As Range
Dim cboVal As String
Dim cboIdx As Long

frmxldSplash.Show

pzLoadList2Lists

Application.DisplayAlerts = False

kList1 = data.Range("A1").Value
klist2 = data.Range("A2").Value

'this populates the Data Validation lists
Set cell = dv.Range(kList1)
fzCreateValidationList1 cell
fzCreateValidationList2 cell.Offset(1, 0), 1, cell

'this populates the combo boxes
fzPopulatList1
cboIdx = 1
On Error Resume Next
cboVal = Application.Evaluate(ThisWorkbook.Names _
("__List1").RefersTo)
cboIdx = Application.Evaluate(ThisWorkbook.Names _
("__ListIndex").RefersTo)
On Error GoTo 0
'if we have saved the value of combo last time, restore it
If cboVal < "" Then
combo.cboPrimary = cboVal
End If
fzPopulatList2 cboIdx
On Error Resume Next
cboVal = Application.Evaluate(ThisWorkbook.Names _
("__List2").RefersTo)
On Error GoTo 0
If cboVal < "" Then
combo.cboSecondary = cboVal
End If

Application.DisplayAlerts = True

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Philip J Smith" wrote in message
...
Hi Bob.

Thanks for your help last week.

If you can spare a little more time, I have one further question.

I have been trying to amend the code so that the selections are not reset
every time the workbook opens, that is the last selection is to be
retained
until the user changes it.

Please, could you indicate what needs to be changed to achieve this
objective? My attempts have been described below.

So as not to lose code during testing I have tried to "text out" by
inserting a " ' " before code in the following cases.

.Clear and .ListIndex =0 in both fzPopulateList1() and fzPopulateList2().

I thought that some combination of these would stop the reset.

The With statements in pzLoadList2Lists()

I think that the last one stops the updating of the ranges on opening, but
that's fine as the lists are of fixed length and update according to
formulae.

Regards

Phil


"Bob Phillips" wrote:

Philip,

That example is assuming that the worksheet that the combobox is on has a
codename of combo. Either change the codename to combo, or the code to
the
worksheet codename, and it should be okay.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Philip J Smith" wrote in
message
...
Hi
I want a user to select a year 2001 to 2010, and then based on this
selection choose a (Friday) weekend date. Other calculations are
dependant
upon the results from this last selection.

I can do this without programming using data validation and the
indirect
function. However I wanted to try to do it using combo boxes.

In the past I have only used the combo box from the forms toolbar and
the
input range does not recognise the INDIRECT function, or at least does
not
behave in the same way as data validation.

When using the combo box from the control toolbox toolbar the indirect
function does not appear to work when =INDIRECT is entered in the
ListFillRange box in properties.

I searched the discussion groups for a solution and I was directed to
www.xldynamic.com/source/xld.Dropdowns.html from multiple postings.

I copied / imported all of the code and modified it to meet my
requirements
re worksheet names (I replaced "data" with "WeekEndingDates") in the
module
mComboMaintain and elsewhere as required.

When I open the workbook, after a while an error message appears

Compile error, variable not defined.

When debugging "combo" is highlighted in blue in the line
"With combo.cboPrimary"

The complete code is given below. Can anyone indicate where I need to
look?

Regards
Phil Smith

Option Explicit

'---------------------------------------------------------------------
Public Function fzPopulatList1()
'---------------------------------------------------------------------
Dim i As Long

Application.EnableEvents = False
'On Error GoTo pl1_exit

With combo.cboPrimary
.Clear
For i = 2 To Range(kList1Hnd).Count + 1
.AddItem WeekEndingDates.Cells(1, i).Value
Next i

Application.EnableEvents = True
.ListIndex = 0
End With

pl1_exit:
Application.EnableEvents = True

End Function

Phil,

Is this referring to the value being retrieved when you re-open the
workbook, or initial setup?

Do those dates display okay before you re-open, that is when first loaded
from their data location?

You could try saving the date in that format rather than as the serial date
in the Refersto line

'---------------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'---------------------------------------------------------------------
ThisWorkbook.Names.Add Name:="__List1", _
RefersTo:=combo.cboPrimary.Value
ThisWorkbook.Names.Add Name:="__ListIndex", _
RefersTo:=combo.cboPrimary.ListIndex + 1
ThisWorkbook.Names.Add Name:="__List2", _
RefersTo:=Format(combo.cboSecondary.Value, "dd/mm/yyyy")
End Sub


Are you UK based? I see you aren't using US date style.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Philip J Smith" wrote in message
...
Many Thanks Bob.

The selections in cboSecondary are dates in the format dd-mm-yyy. On
opening the selection is displayed as a text of the date serial number.

I think that I need to insert a formatting line just below
RefersTo:Combo.cboSecondary.Value
which looks something like
cboSecondary.value=Format(cboSecondary.value,"dd/mm/yyyy")

But I can't get it to work, what am I missing, or is this the wrong place
to
achieve what I am attempting to do.

Regards
Phil

"Bob Phillips" wrote:

Phil,

It is not quite that simple I am afraid, the thing just wasn't designed
that
way. The problem in changing the populate routines is that they are used
whenever a primary selection is made, so it is dangerous.

To achieve what you want, I would save the values on closedown, and
restore
them at next open. To do this I would save the values in workbook defined
names on workbook close, and read them on workbook open. No need to touch
the populate routines

Here is a (new) Workbook_BeforClose procedure, and an amended
Workbook_Open
procedure that does just that. Just replace all code in ThisWorkbook with
this code


Option Explicit

'---------------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'---------------------------------------------------------------------
ThisWorkbook.Names.Add Name:="__List1", _
RefersTo:=combo.cboPrimary.Value
ThisWorkbook.Names.Add Name:="__ListIndex", _
RefersTo:=combo.cboPrimary.ListIndex + 1
ThisWorkbook.Names.Add Name:="__List2", _
RefersTo:=combo.cboSecondary.Value
End Sub

'---------------------------------------------------------------------
Private Sub Workbook_Open()
'---------------------------------------------------------------------
Dim cell As Range
Dim cboVal As String
Dim cboIdx As Long

frmxldSplash.Show

pzLoadList2Lists

Application.DisplayAlerts = False

kList1 = data.Range("A1").Value
klist2 = data.Range("A2").Value

'this populates the Data Validation lists
Set cell = dv.Range(kList1)
fzCreateValidationList1 cell
fzCreateValidationList2 cell.Offset(1, 0), 1, cell

'this populates the combo boxes
fzPopulatList1
cboIdx = 1
On Error Resume Next
cboVal = Application.Evaluate(ThisWorkbook.Names _
("__List1").RefersTo)
cboIdx = Application.Evaluate(ThisWorkbook.Names _
("__ListIndex").RefersTo)
On Error GoTo 0
'if we have saved the value of combo last time, restore it
If cboVal < "" Then
combo.cboPrimary = cboVal
End If
fzPopulatList2 cboIdx
On Error Resume Next
cboVal = Application.Evaluate(ThisWorkbook.Names _
("__List2").RefersTo)
On Error GoTo 0
If cboVal < "" Then
combo.cboSecondary = cboVal
End If

Application.DisplayAlerts = True

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Philip J Smith" wrote in
message
...
Hi Bob.

Thanks for your help last week.

If you can spare a little more time, I have one further question.

I have been trying to amend the code so that the selections are not
reset
every time the workbook opens, that is the last selection is to be
retained
until the user changes it.

Please, could you indicate what needs to be changed to achieve this
objective? My attempts have been described below.

So as not to lose code during testing I have tried to "text out" by
inserting a " ' " before code in the following cases.

.Clear and .ListIndex =0 in both fzPopulateList1() and
fzPopulateList2().

I thought that some combination of these would stop the reset.

The With statements in pzLoadList2Lists()

I think that the last one stops the updating of the ranges on opening,
but
that's fine as the lists are of fixed length and update according to
formulae.

Regards

Phil


"Bob Phillips" wrote:

Philip,

That example is assuming that the worksheet that the combobox is on
has a
codename of combo. Either change the codename to combo, or the code to
the
worksheet codename, and it should be okay.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Philip J Smith" wrote in
message
...
Hi
I want a user to select a year 2001 to 2010, and then based on this
selection choose a (Friday) weekend date. Other calculations are
dependant
upon the results from this last selection.

I can do this without programming using data validation and the
indirect
function. However I wanted to try to do it using combo boxes.

In the past I have only used the combo box from the forms toolbar
and
the
input range does not recognise the INDIRECT function, or at least
does
not
behave in the same way as data validation.

When using the combo box from the control toolbox toolbar the
indirect
function does not appear to work when =INDIRECT is entered in the
ListFillRange box in properties.

I searched the discussion groups for a solution and I was directed
to
www.xldynamic.com/source/xld.Dropdowns.html from multiple postings.

I copied / imported all of the code and modified it to meet my
requirements
re worksheet names (I replaced "data" with "WeekEndingDates") in the
module
mComboMaintain and elsewhere as required.

When I open the workbook, after a while an error message appears

Compile error, variable not defined.

When debugging "combo" is highlighted in blue in the line
"With combo.cboPrimary"

The complete code is given below. Can anyone indicate where I need
to
look?

Regards
Phil Smith

Option Explicit

'---------------------------------------------------------------------
Public Function fzPopulatList1()
'---------------------------------------------------------------------
Dim i As Long

Application.EnableEvents = False
'On Error GoTo pl1_exit

With combo.cboPrimary
.Clear
For i = 2 To Range(kList1Hnd).Count + 1
.AddItem WeekEndingDates.Cells(1, i).Value
Next i

Application.EnableEvents = True
.ListIndex = 0
End With

pl1_exit:
Application.EnableEvents = True

End Function