Dynamic Dependant Downloads

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

Absolutely Brilliant!! I knew I was being thick!

Thanks for the prompt response!

Regards

Phil

 

[Guest]

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]

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)

 

[Guest]

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

 

[Guest]

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]

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)

 

[Guest]

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]

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)

 

[Guest]

Hi Bob.

Still a continuing problem I’m afraid. The chosen item is continuing to be
saved as the text of a serial number.

During operation after making a selection from the primary combo the
secondary combo box displays selections as “m/d/yyyyâ€, “m†and “d†can be
either 1 or 2 digits. Occasionally, and I don’t know why, the secondary
combo shows date labels in the format dd/mm/yyyy.

Is it possible for the pzPopulateList2 to convert the column of dates
number format “dd-mmm-yy†into an array of text dates format “dd-mmm-yyâ€.

I know that this is non-standard as VBA assumes that the US date format is
required, but developers with clients / employers who use the British date
format must have found ways to work around this. If anyone has it I would be
interested in seeing it. George Bernard Shaw once quipped that “America and
England are two cultures separated by a common languageâ€. I think this is
case that proves his point.

Without changing the code I think that the following workaround might work -
I need to hold the data in two separate tables. The first calculates the
dates and holds them as values. The second holds the data as labels using
=text(A1,â€dd-mmm-yyâ€) and it is this table that is used to populate the
secondary combo box.

So as not to take up any more of your time I will move forward on this
basis. Thanks once again for your help.

Regards

Phil

 

[Bob Phillips]

Phil,

I have so many problems with dates in VBA. I know the rules, but forget
them. I had a book recently that opens a CSV file with dates. If you open
the file in Excel, the dates are correct, if you open it in VBA a date of
12/08/1984 gets seen as 8th Dec. I wrote a little routine to force it into
UK dates which works fine in my test environment, but causes an application
crash in production!

Send me the workbook, and I'll see what I can do.

Bob

--
---
HTH

Bob

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

 

[John Taylor]

Bob,

I can't help with the problem being discussed here, but maybe I can give you
a lead on your "dates" problem (if you're using xl2003) because I had a
similar one recently.

I had a macro that imported a csv file, and it worked fine in xl97. When I
ran it in XL2003, however, some dates (all dd/mm/yyyy format) were imported
in the wrong format, and on checking the MS Knowledgebase (911750) I found
that it's a known problem. There is a hotfix available, and also a
workaround.

After some help from the kind folks on this newsgroup I was able to
successfully use the workaround.

Regards,

John

 

[Bob Phillips]

Thanks John, I will check the article.

As I said, I have had all sorts of date problems in the past, but I can
usually get around them. I thought I had gotten around this one, but in
production something else failed (seemingly unrelated, but worked when we
took out my 'fix').

--
---
HTH

Bob

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

 

[Guest]

Hi Bob,

Many thanks for the offer, but I can't accept. Your help to date in
amending the code has been great I think I've learned quite a bit.

The workaround I suggested only partially works.

Changing the data to text fixes the problem as regards the swapping between
calendar conventions, but there is something in the code that still converts
the date string saved on closing into a date serial number string.

I selected a year 2008, then selected 25-Jan-08 (as a string) and then saved
the file. Upon opening the following sequence of labels appeared in the
cboSecondary frame.

cboPrimary cboSecondary Note
2008 25-Jan-08 1
2001 05-Jan-01 2 & 3
2008 04-Jan-08 4 & 5
2008 25-Jan-08 6
2008 39472 7

Notes
1. Effect of code not yet displayed
2. Code from fzPopulateList1() resets cboPrimary to first item on the list
(.ListIndex = 0)
3. Code from fzPopulateList2() resets cboSecondary to first item on the
list (.ListIndex = 0)
4. Workbook_open restores the saved value of cboPrimary.
5. cboSecondary defaults to the first item on the list.
6. Workbook_open restores the saved string for cboSecondary.
7. Something converts the date string into a serial number string.

I think this occurs somewhere in the following code from Workbook_Open

cboVal = Application.Evaluate(ThisWorkbook.Names _
("__List2").RefersTo)
On Error GoTo 0
If cboVal <> "" Then
Combo.cboSecondary = cboVal

I am guessing here so I have no idea why this might happen. I don’t
think that it is in the declaration of cboValue, so it is either when cboVal
has the content of “__List2†passed to it, or when cboSecondary has this
value passed to it, but I can’t see why and I can’t work out how to amend it.
And of course I am probably wrong to attribute this format change to this
code.

The following syntax is probably wrong for VBA, but could the final line
above be amended to read something like.

If cboVal <> "" Then _
combo.cboSecondary = TEXT(VALUE(cboVal),â€dd-mmm-yyâ€)

Regards

Phil