Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
Hi Bob.
Still a continuing problem Im 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 dont 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" wrote: 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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) "Philip J Smith" wrote in message ... 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" wrote: 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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" wrote in message ... 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) |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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) "John Taylor" wrote in message ... 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" wrote in message ... 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) |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Dependant Downloads
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 dont 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 cant see why and I cant 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 "Bob Phillips" wrote: 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) "Philip J Smith" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing downloads into Excel | New Users to Excel | |||
How do I resolve unsuccessful security downloads for excel? | Excel Discussion (Misc queries) | |||
Comma delimiter for csv downloads? | Excel Discussion (Misc queries) | |||
File Downloads in VBA | Excel Discussion (Misc queries) | |||
How do I find downloads for workbooks for Accounting homework? | Excel Worksheet Functions |