Great. I had assumed for some reason that you were US based.

Post back if you have any other problems.

--
---
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.

Many thanks for the follow up. Yes I am UK based. I don't mind using the
US format but some colleagues do!

I think that the code modification will fix it.

Regards

Phil

"Bob Phillips" wrote:

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