Misc problems and questions.

[Djblois]

I want to do a few things in my add-in that I have no idea how to do:

1) I want to do a custom sort. This would be the sort order: 1, 6, 3, 10, 15, 21, 7, 5, 9, 11, 22, 23. I know this is possible in Excel 2007 but I was wondering if I can do it with VBA in 2003?

2) I created a form that will give my users a couple of thousand ways of viewing their data. The form itself I have complete the way I want it to work. However, the code to run the report seems to long the only way I can think to do it. Here is a the form with the form code. What I want it to do is create it so the user can choose what they want in the first column of the pivottable and then the 2 and so on (up to a maximum of 6 columns) and then choose the date grouping and if they want any other information in in the data columns and finally they get to choose the data they want to view. However the only way I can think about doing the code is nesting about 10 if's deep and not only would this slow things down but it would take roughly 500 lines of code minimum to work. Is there any other way of doing this? If you need any more information, don't hesitate to ask.

[Bob Phillips]

1) I want to do a custom sort. This would be the sort order: 1, 6, 3, 10, 15, 21, 7, 5, 9, 11, 22, 23. I know this is possible in Excel 2007 but I was wondering if I can do it with VBA in 2003?
You would need to create a custom list with those values and then do a special sort using that custom list.


2) I created a form that will give my users a couple of thousand ways of viewing their data. The form itself I have complete the way I want it to work. However, the code to run the report seems to long the only way I can think to do it. Here is a the form with the form code. What I want it to do is create it so the user can choose what they want in the first column of the pivottable and then the 2 and so on (up to a maximum of 6 columns) and then choose the date grouping and if they want any other information in in the data columns and finally they get to choose the data they want to view. However the only way I can think about doing the code is nesting about 10 if's deep and not only would this slow things down but it would take roughly 500 lines of code minimum to work. Is there any other way of doing this? If you need any more information, don't hesitate to ask.


You can simplify some of the code as show in the example below.



Private Sub CustomerCheck()
If C1Customer Then
C2Customer.Enabled = False
C3Customer.Enabled = False
C4Customer.Enabled = False
C5Customer.Enabled = False
C6Customer.Enabled = False
HCustomer.Enabled = False
ElseIf C2Customer Then
C1Customer.Enabled = False
C3Customer.Enabled = False
C4Customer.Enabled = False
C5Customer.Enabled = False
C6Customer.Enabled = False
HCustomer.Enabled = False
ElseIf C3Customer Then
C1Customer.Enabled = False
C2Customer.Enabled = False
C4Customer.Enabled = False
C5Customer.Enabled = False
C6Customer.Enabled = False
HCustomer.Enabled = False
ElseIf C4Customer Then
C1Customer.Enabled = False
C2Customer.Enabled = False
C3Customer.Enabled = False
C5Customer.Enabled = False
C6Customer.Enabled = False
HCustomer.Enabled = False
ElseIf C5Customer Then
C1Customer.Enabled = False
C2Customer.Enabled = False
C4Customer.Enabled = False
C5Customer.Enabled = False
C6Customer.Enabled = False
HCustomer.Enabled = False
ElseIf C6Customer Then
C1Customer.Enabled = False
C2Customer.Enabled = False
C3Customer.Enabled = False
C4Customer.Enabled = False
C5Customer.Enabled = False
HCustomer.Enabled = False
ElseIf HCustomer Then
C1Customer.Enabled = False
C2Customer.Enabled = False
C3Customer.Enabled = False
C4Customer.Enabled = False
C5Customer.Enabled = False
C6Customer.Enabled = False
Else
C1Customer.Enabled = True
C2Customer.Enabled = True
C3Customer.Enabled = True
C4Customer.Enabled = True
C5Customer.Enabled = True
C6Customer.Enabled = True
HCustomer.Enabled = True
End If
End Sub



Private Sub CustomerCheck()
Select Case True
Case C1Customer, C2Customer, C3Customer, C4Customer, _
C5Customer, C6Customer, HCustomer:
C2Customer.Enabled = False
C3Customer.Enabled = False
C4Customer.Enabled = False
C5Customer.Enabled = False
C6Customer.Enabled = False
HCustomer.Enabled = False
Case Else:
C1Customer.Enabled = True
C2Customer.Enabled = True
C3Customer.Enabled = True
C4Customer.Enabled = True
C5Customer.Enabled = True
C6Customer.Enabled = True
HCustomer.Enabled = True
End Select
End Sub

[Djblois]

xld,

1) After I create the list, how do I do a special sort?
2) That is very useful, even though it wasn't my question. Thanks for the help on that, it will cut down on the code in the form but do you have any idea on how to do the form without all the code. THis is an example of what I was going to do:

if c1customer then
if c2nonthing then
if c2customer then
if
if c2ccity then
if c2cstate then
if c2czipcode then
end if

[Djblois]

xld,

1) After I create the list, how do I do a special sort?
2) That is very useful, even though it wasn't my question. Thanks for the help on that, it will cut down on the code in the form but do you have any idea on how to do the form without all the code. THis is an example of what I was going to do:

if c1customer then
if c2nonthing then
elseif c2customer then
if c3ccity then
end if
if c4cstate then
end if
if c5czipcode then
end if
if c6ccategory then
end if
elseif c2ccity then
elseif c2cstate then
elseif c2czipcode then
end if
end if

and I would have to repeat that for every column 1, every column2, every column3 etc. options. Of course the user can't choose the same information twice that is why I disable the items as you choose them.

[Bob Phillips]

xld,

1) After I create the list, how do I do a special sort?
2) That is very useful, even though it wasn't my question. Thanks for the help on that, it will cut down on the code in the form but do you have any idea on how to do the form without all the code. THis is an example of what I was going to do:

if c1customer then
if c2nonthing then
elseif c2customer then
if c3ccity then
end if
if c4cstate then
end if
if c5czipcode then
end if
if c6ccategory then
end if
elseif c2ccity then
elseif c2cstate then
elseif c2czipcode then
end if
end if
and I would have to repeat that for every column 1, every column2, every column3 etc. options. Of course the user can't choose the same information twice that is why I disable the items as you choose them.

I only took a quick look at it, but the amount of code just overwhelmed me. To help properly I would need to sit down with you and understand your business objectives, and look to design it in a simpler manner. Sorry.

[Djblois]

its ok, no problem and thank you for all the help you have give me

[Bob Phillips]

This is the sort of code you will need to do the sort



Sub CustomSort()
Dim iCustomList As Long
Dim i As Long
Dim iLBound As Long
Dim ary

iCustomList = 0
For i = 1 To Application.CustomListCount
ary = Application.GetCustomListContents(i)
iLBound = LBound(ary)
If ary(iLBound) = 1 And ary(iLBound + 1) = 6 And _
ary(iLBound + 2) = 3 Then
iCustomList = i
Exit For
End If
Next i
If iCustomList = 0 Then
MsgBox "Custom List not found"
Else
Columns("A:A").Sort Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=iCustomList + 1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
End Sub

[Djblois]

xld,

I will create a sheet in another workbook to look up from. However, how do I attach that macro to that sheet?

[Bob Phillips]

Why would you do that?

[Djblois]

I thought that is what you were saying to do. Sorry, I think I see now. I create it in the code.

[Djblois]

Xld,

Dim iCustomList As Long
Dim i As Long
Dim iLBound As Long
Dim ary

iCustomList = 0
For i = 1 To Application.CustomListCount
ary = Application.GetCustomListContents(i)
iLBound = LBound(ary)
If ary(iLBound) = 1 And ary(iLBound + 1) = 6 And _
ary(iLBound + 2) = 3 And ary(iLBound + 3) = 10 And ary(iLBound + 4) = 15 And _
ary(iLBound + 5) = 21 And ary(iLBound + 6) = 7 And ary(iLBound + 7) = 5 And _
ary(iLBound + 8) = 5 And ary(iLBound + 9) = 9 And ary(iLBound + 10) = 11 And _
ary(iLBound + 11) = 22 And ary(iLBound + 12) = 23 Then
iCustomList = i
Exit For
End If
Next i
If iCustomList = 0 Then
MsgBox "Custom List not found"
End if
Else_
Header:=xlGuess, _
OrderCustom:=iCustomList + 1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If


This is what I have now XLD. I am trying to get it to sort the a column with this custom order (1,6,3,10,15,21,7,5,9,11,22,23)

[Djblois]

It keeps giving me the error subscript out of range where I build the list

[Djblois]

bump, the custom sort is a priority for my add-in now. It will save my bosses secretary about 1 hour each week.

[Bob Phillips]

I chopped off the sort bit to test it (seeing as it was incomplete and couldn't possibly work).

The problem is that not all the custojm lists will be the same size as yours (there arer onlky 7 days ina week not 12 for instance), so it is best to check that first then you won't test index 12 when there isn't an index 12. Also, you repeated one of your values



Dim iCustomList As Long
Dim i As Long
Dim iLBound As Long
Dim iUBound As Long
Dim ary

iCustomList = 0
For i = 1 To Application.CustomListCount
ary = Application.GetCustomListContents(i)
iLBound = LBound(ary)
iUBound = UBound(ary)
If iUBound - iLBound + 1 = 12 Then
If ary(iLBound) = 1 And ary(iLBound + 1) = 6 And _
ary(iLBound + 2) = 3 And ary(iLBound + 3) = 10 And _
ary(iLBound + 4) = 15 And ary(iLBound + 5) = 21 And _
ary(iLBound + 6) = 7 And ary(iLBound + 7) = 5 And _
ary(iLBound + 8) = 9 And ary(iLBound + 9) = 11 And _
ary(iLBound + 10) = 22 And ary(iLBound + 11) = 23 Then
iCustomList = i
Exit For
End If
End If
Next i
If iCustomList = 0 Then
MsgBox "Custom List not found"
End If

[johnske]

...1) I want to do a custom sort. This would be the sort order: 1, 6, 3, 10, 15, 21, 7, 5, 9, 11, 22, 23. I know this is possible in Excel 2007 but I was wondering if I can do it with VBA in 2003?...you can sort by as many columns as you like by doing a multiple sort. The simplest way to explain how to do this is to write a macro with the sort order reversed. For instance, you say you want to sort by column1, then by column6, then by column3, then by column10, then by column15, then by column21, then by column7, then by columns, 5, 9, 11, 22, and 23.

You write your procedure to sort first by column23, then 22, 11, 9, 5, 7, 21, 15, 10, 3, 6, and finally by column1. (i.e. reversed)

Here's an example where you may want to sort 6 columns, first by column A, then by column B, C, D, E, then F. Reverse this like so...

Option Explicit
'
Sub SixColumnSort()
'put your own orders in below
Application.ScreenUpdating = False
With Range("A1:M100")
'
.Sort Key1:=Range("F1"), _
Order1:=xlAscending, _
Header:=xlYes, _
MatchCase:=False, _
Orientation:=xlTopToBottom
'
.Sort Key1:=Range("E1"), _
Order1:=xlAscending, _
Header:=xlYes, _
MatchCase:=False, _
Orientation:=xlTopToBottom
'
.Sort Key1:=Range("D1"), _
Order1:=xlAscending, _
Header:=xlYes, _
MatchCase:=False, _
Orientation:=xlTopToBottom
'
.Sort Key1:=Range("C1"), _
Order1:=xlAscending, _
Header:=xlYes, _
MatchCase:=False, _
Orientation:=xlTopToBottom
'
.Sort Key1:=Range("B1"), _
Order1:=xlAscending, _
Header:=xlYes, _
MatchCase:=False, _
Orientation:=xlTopToBottom
'
.Sort Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlYes, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Application.ScreenUpdating = True
End Sub


EDIT: re-written to provide a better explanation here (http://xlvba.3.forumer.com/index.php?showtopic=310&hl=)

[Djblois]

I chopped off the sort bit to test it (seeing as it was incomplete and couldn't possibly work).

The problem is that not all the custojm lists will be the same size as yours (there arer onlky 7 days ina week not 12 for instance), so it is best to check that first then you won't test index 12 when there isn't an index 12. Also, you repeated one of your values



Dim iCustomList As Long
Dim i As Long
Dim iLBound As Long
Dim iUBound As Long
Dim ary

iCustomList = 0
For i = 1 To Application.CustomListCount
ary = Application.GetCustomListContents(i)
iLBound = LBound(ary)
iUBound = UBound(ary)
If iUBound - iLBound + 1 = 12 Then
If ary(iLBound) = 1 And ary(iLBound + 1) = 6 And _
ary(iLBound + 2) = 3 And ary(iLBound + 3) = 10 And _
ary(iLBound + 4) = 15 And ary(iLBound + 5) = 21 And _
ary(iLBound + 6) = 7 And ary(iLBound + 7) = 5 And _
ary(iLBound + 8) = 9 And ary(iLBound + 9) = 11 And _
ary(iLBound + 10) = 22 And ary(iLBound + 11) = 23 Then
iCustomList = i
Exit For
End If
End If
Next i
If iCustomList = 0 Then
MsgBox "Custom List not found"
End If


xld,

It always tells me Custom List not found.

[Djblois]

bump