Challenge: Triangle Area Select tool

[xluser2007]

Hi Bob, many thanks for your kind interest in this problem.

This doesn't change the r range so it shouldn't affect the union, but just uses row x in the activate rather that row 1 as you had it.

I initially meant that the range "r" actually changes throughout the code. But I believe you meant defining the ac right up the top of the cdoe, where "r" is first defined, not at the bottom. As such, your method indeed will work. Please correct me if I misunderstood you.

I am not sure and I don't think I have the patience to read all 11 pages of that article, but MD's addin was already fully functional, just making that change should ensure that the toolbar is always setup. The other changes re for the function changes that you require.

I am also not sure that you mean error handling, or at least error catching. It seems to me that you want it to behave differently if there is no room to behave normally, which is a different thing. In that case, it needs to be coded accordingly.

Bob, md. I hope taht when I meant to use Johnske's Addin example that i did not come across that I had any issues with md's approach in anyway.

Bob, I wasn't sure what you had meant by:

Try moving the toolbar build code to Workbook_Open.

As such I searched around anf found that johnske had done it in his generic Add-in creation template. However adding in this code required all the other modules in that article (from what I could understand), and as such, I thought to ask you both for the article, so that I could amend it appropriately. Though it seems that the solution may be a quick addition to md's existing add-in event code.

Could you please assist with this code Bob?

Also Bob, thank you for your elegant code in post #28. I was wondering when I was going to see your solution with Enums .

Based on some testing, I was wondering if it could be corrected for the following issues:

1. single row selected - as per Malcolm's reference to post #27, which malcolms code addresses.
2. checks it will fit on sheet
3. check that only a range is selected
4. multiple areas selected
5. multi-row and multi-column block uses first column only

I always love reading your great code, I am keen to understand how it could be adapted for the above changes.

[xluser2007]

As promised, see attached which contains a new toolbar with your images on.
Addresses:
-single row selected
-checks it will fit on sheet
-made a guess at which cell is to be the active cell in each case
-consolidated single sub to handle all 6 cases
-a range not selected (say a chart instead) when macro started
-multiple areas selected (it chooses the first one (that was selected))
-single cell selected
-multi-row and multi-column block uses first column only

I leave you to create an add-in from it and to show/hide the toolbar as appropriate.

Hi p45cal, hope you've been well.

Thank you kindly for your fantastic coding - works beautifully and adresses all the main question based on a coupole of days of testing.

I only modified it alightly to display some additional error messages. Aside from that everything is great.

Also, it was a cool touch to make the row and columns the same height and width to make it easier to check that only half-square selections are made in the triangle macros .


BTW, what are ddd and xxx supposed to mean. I want to just rename them based on their meaning so that I can understand and make sense of the code.

I am not sure how to make the add-in the way that malcolm did it. I'll give it a go though based on his method in this thread.

[Bob Phillips]

Bob, md. I hope taht when I meant to use Johnske's Addin example that i did not come across that I had any issues with md's approach in anyway.

Not at all, I was just thinking that you maybe hadn't fully appreciated the full scope of Malcolm's work.

Bob, I wasn't sure what you had meant by:

This was because Malcolm was building it in the addin-install, I was just suggesting an alternative place.

Also Bob, thank you for your elegant code in post #28. I was wondering when I was going to see your solution with Enums .

I think I rarely wite any serious code now that doesn't have enums

Based on some testing, I was wondering if it could be corrected for the following issues:

1. single row selected - as per Malcolm's reference to post #27, which malcolms code addresses.
2. checks it will fit on sheet
3. check that only a range is selected
4. multiple areas selected
5. multi-row and multi-column block uses first column only

I always love reading your great code, I am keen to understand how it could be adapted for the above changes.

I think the code catered for some of this, but I will package it into an addin using Malcoms icons, and see how close I get. May take a while though.

[p45cal]

BTW, what are ddd and xxx supposed to mean. I want to just rename them based on their meaning so that I can understand and make sense of the code.

ddd and xxx are variable names chosen without forethought.
The way this code works is based on a grid, like your school times table, but instead of a times tables it's an addition table and a division table:Attachment 1501
You can see in the first one (the addition table) that there's a diagonal of 11s, all values to the left and above that diagonal are less than 11, all values above 11 are below and to the right. The value 11 is one more than the number of squares on a side. The ddd is, in this case, the 11.
In the division table, you can see a pattern of ones on the opposite diagonal. This doesn't need calculationg, it's always 1. So where the slope is -1, there's no need for a value for ddd.
xxx is the number in each square of the grids above, calculated but never actually placed in the cell. It is compared against ddd or 1 in the code.

A more useful name for each of these variables? Up to you!

[xluser2007]

Thanks p45cal for your gereat explanations, as always. The code makes much more sense to me now .

Also with regard to creating the addin, I have pasted md's addin code into a workbook with buttons and your code (only slightly modified as mentioned in my previous post).

Could yourself, Bob or md please help put the finalising touches on the addin creation code or settings in the attached workbook please e.g. the appropriate additions to the workbook event code, and any other settings that need to be toggled?

[xluser2007]

I think the code catered for some of this, but I will package it into an addin using Malcoms icons, and see how close I get. May take a while though.

Thanks Bob, fully appreciate that you are busy and appreciate your efforts in taching and helping me.

I continue to learn a great deal from this thread from your p45cal and malcolm .

[xluser2007]

Thanks p45cal for your gereat explanations, as always. The code makes much more sense to me now .

Also with regard to creating the addin, I have pasted md's addin code into a workbook with buttons and your code (only slightly modified as mentioned in my previous post).

Could yourself, Bob or md please help put the finalising touches on the addin creation code or settings in the attached workbook please e.g. the appropriate additions to the workbook event code, and any other settings that need to be toggled?

Hi Bob, md and p45cal,

Could you please, if you have the time, assis with creating an addin- from the attached workbook (with all code pasted in and also Buttons)?


The workbook is as attached in post #46.

Any help sincerely appreciated.

[xluser2007]

Hi All,

Could you please assist me in creating the addin code for the workbook in post #46.

Any help sincerely appreciated.

Kind regards,

[Bob Phillips]

There is no workbook in post #46.

[xluser2007]

There is no workbook in post #46.

Sorry Bob , I meant post #45.

[Bob Phillips]

.

[xluser2007]

Bob,

Many thanks for helping me to complete the problem at hand.

For my understanding, may I confirm that the key changes you made were the following:

1. changing the Thisworkbook code from:

[VBA]
Private Sub Workbook_AddinInstall()
CreateCustomCommandBar
End Sub

Private Sub Workbook_AddinUninstall()
DeleteCustomCommandBar
End Sub
[/VBA]

to:

[VBA]
Option Explicit

Private Sub Workbook_Open()
CreateCustomCommandBar
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCustomCommandBar
End Sub[/VBA]

May I ask, why the second works and the first doesn't?

2. Saved it as an *.xla file?

Are there any other changes made.

Sincerely, I want to thank p45cal, md and yourself for your kidn help and persisntence in solving all the main issues in this thread .

I'll test it for a couple of days and let you know if I have any remaining queries.

[mdmackillop]

This first code is run when the add-in is installed, the second when the add-in is "opened" i.e. when you open Excel and the add-in is loaded.

If you wanted the add-in loaded for only specific workbooks, you could add WorkBook open events to those
eg
[VBA]
Private Sub Workbook_Open()
AddIns("Actuarial Triangle Selector").Installed = True
End Sub
[/VBA]
using the Workbook_AddinInstall code in the Add-in.

Does that make sense?

[xluser2007]

This first code is run when the add-in is installed, the second when the add-in is "opened" i.e. when you open Excel and the add-in is loaded.

If you wanted the add-in loaded for only specific workbooks, you could add WorkBook open events to those
eg
[vba]
Private Sub Workbook_Open()
AddIns("Actuarial Triangle Selector").Installed = True
End Sub
[/vba] using the Workbook_AddinInstall code in the Add-in.

Does that make sense?

Hi md,

thanks for your response.

Does that mean that the aletrnative Thisworkbook code in the addin file could read as follows?:

[vba]
Private Sub Workbook_Open()
AddIns("Actuarial Triangle Selector").Installed = True
End Sub

Private Sub Workbook_AddinInstall()
CreateCustomCommandBar
End Sub

Private Sub Workbook_AddinUninstall()
DeleteCustomCommandBar
End Sub
[/vba]
also, i have found a slight issue with the original code, when selecting non range objects and running the macros, anmely when slecting a chart and running the "TopLeft" Traingle Select macro, i get a 'Runtime Error - 13' Type mismatch. I thought that this was error handled in the code below:

[vba]
Sub Triangle_Diagonal_Select(Rng As Excel.Range, Slope As Long, Filling As String)

Dim ac As Excel.Range
Dim newrng As Excel.Range
Dim cll As Excel.Range
Dim xxx As Double
Dim TypeCombi As String
Dim ddd As Long

If Not Selection Is Nothing Then

If UCase(TypeName(Rng)) <> "RANGE" Then

Call MsgBox("You have currently selected a " & UCase(TypeName(Rng)) _
& vbCrLf & "" _
& vbCrLf & "Please select a COLUMN or a ROW of CELLS and re-run to continue." _
, vbCritical, "This tool only works on Selected Cells!")

Else

Set Rng = Rng.Areas(1)

If Rng.Cells.Count > 1 Then

If Rng.Rows.Count = 1 Then

' CHECK it's going to fit:

If Rng.Columns.Count <= Rng.Row Then
Set Rng = Rng.Cells(1).Resize(Rng.Columns.Count, 1).Offset(-Rng.Columns.Count + 1)

Else

MsgBox "Won't fit above selection - try again"
Exit Sub

End If

Else

Set Rng = Rng.Columns(1)

If Rng.Rows.Count > Columns.Count - Rng.Column + 1 Then

MsgBox "Won't fit to the right of selection - try again"
Exit Sub

End If

End If

' Now a valid starting range (rng) has been established:
TypeCombi = Slope & Filling

' QUESTION: WHICH SHOULD BE ACTIVE CELL IN EACH CASE? - I'VE GUESSED:
Set ac = Rng.Cells(Rng.Cells.Count) '1T,-1B,1N,1B
Select Case TypeCombi

' Case "-1T", "-1N", "1T": Set newrng = Rng.Cells(1): Set ac = Rng.Cells(1) '-1T,-1N,1T
Case "-1T", "-1N": Set newrng = Rng.Cells(1): Set ac = Rng.Cells(1) '-1T,-1N,1T
Case Else: Set newrng = Rng.Cells(Rng.Rows.Count)

End Select

ddd = Rng.Row + Rng.Column + Rng.Rows.Count - IIf(TypeCombi = "1T", 0, 1)

For Each cll In Rng.Resize(, Rng.Rows.Count).Cells

Select Case Slope

Case -1
xxx = (cll.Column - Rng.Column + 1) / (cll.Row - Rng.Row + 1)
Select Case Filling
Case "B": If xxx <= 1 Then Set newrng = Union(newrng, cll) '-1B
Case "N": If xxx = 1 Then Set newrng = Union(newrng, cll) '-1N
Case "T": If xxx >= 1 Then Set newrng = Union(newrng, cll) '-1T
End Select

Case Else 'Slope 1

xxx = cll.Column + cll.Row

Select Case Filling
Case "B": If xxx >= ddd Then Set newrng = Union(newrng, cll) '1B
Case "N": If xxx = ddd Then Set newrng = Union(newrng, cll) '1N
Case "T": If xxx < ddd Then Set newrng = Union(newrng, cll) '1T
End Select

End Select

Next cll

newrng.Select

Set Rng = Nothing: Set newrng = Nothing
ac.Activate

End If

End If

Else

Call MsgBox("Please select a COLUMN or a ROW of CELLS and re-run to continue.", _
vbExclamation, "This tool only works on Selected Cells!")


End If

End Sub
[/vba]
Could you please explain why this error-handling is not working? I am guessing it is because we have Dimesnioned Rng as Excel.Range as part of the macro parameters, and if I select a Chart for example, I can't get to the error-handling code below, as it refuses to accpt a non-range selection before running the macro. Could you please explain how to correct for this?

any help appreciated.

[mdmackillop]

Does that mean that the aletrnative Thisworkbook code in the addin file could read as follows?:

No.
Say that you only used this code in a workbook based on a few Actuarial templates, Act1.xlt, Act2.xlt and so on. The Workbook Open code would go in the Template file, the Install code would be in the Add-In. In that way, the Add-In would be loaded only as required.

[Bob Phillips]

I think that you could but you should do

[vba]

Private Sub Workbook_Open()
AddIns("Actuarial Triangle Selector").Installed = False
AddIns("Actuarial Triangle Selector").Installed = True
End Sub

Private Sub Workbook_AddinInstall()
CreateCustomCommandBar
End Sub

Private Sub Workbook_AddinUninstall()
DeleteCustomCommandBar
End Sub
[/vba]

to be sure.

But I have to ask why bother, what I gave you works.

[xluser2007]

No.
Say that you only used this code in a workbook based on a few Actuarial templates, Act1.xlt, Act2.xlt and so on. The Workbook Open code would go in the Template file, the Install code would be in the Add-In. In that way, the Add-In would be loaded only as required.

[vba]Private Sub Workbook_Open()
AddIns("Actuarial Triangle Selector").Installed = False
AddIns("Actuarial Triangle Selector").Installed = True
End Sub

Private Sub Workbook_AddinInstall()
CreateCustomCommandBar
End Sub

Private Sub Workbook_AddinUninstall()
DeleteCustomCommandBar
End Sub[/vba]

Thanks md and Bob for clarifying.

my query was more for learning purposes to understand why Bob's change worked.

BTW, for my error handling issue, I realised that the only change was for me to Dim Rng as VARIANT (not Excel.Range), so that it could accept any Selection type and then move onto the custom error-handling for RANGES only. Do you guys agree with what I've done - is there a better way?

[mdmackillop]

Check that is is a valid range before passing it to the sub routine. Consider
[VBA]
MsgBox TypeName(Selection)
[/VBA]
You can use this result to determine how the code proceeds.

[mdmackillop]

BTW, If anyone does not understand the concept of "Project Creep", I shall point them to this thread!!!

[Bob Phillips]

BTW, If anyone does not understand the concept of "Project Creep", I shall point them to this thread!!!

You mean a typical user

But in many ways MD, it is threads such as these that distinguish VBAX. You wouldn't see anything like tis in MrExcel, it would disappear it millions of threads asking how to count coloured cells