Active command button. (1 Viewer)

[Uncle Gizmo]

Hi all!

I want to detect when any command button is pressed in excel, extract the command button caption, and then use this in a single routine to perform an operation which is in fact highlighting part of a particular column(s).

In access you would use something like this to detect the particular control activated:

'    Dim ctlCurrentControl As Control
'    Set ctlCurrentControl = Screen.ActiveControl

this does not work in excel, the equivalent appears to be:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ctlCurrentControl As Worksheet
    Set ctlCurrentControl = Sheet1
    MsgBox "Name  " & ActiveCell.Value
End Sub

I can get the value of the current cell, but I can't get the value of a "pressed" command button?

I think it may be because the command buttons are in a different collection, they appear to be in a collection called ".ShapeRange" but I am not sure!

I am having a head scratching moment, and I would appreciate if someone could point me in the right direction!

Cheers Tony

 

[clive2002]

CommandBars is the collection i would look in but i really don't think your gonna achieve on this.

Your barking up the wrong tree with shaperange i believe it relates to setting cell ranges and arrays.

Can't help with how u are going to execute your code, I'm not aware of a onclickevent for commandbuttons unless you set an action for each button. You can't use selectionchange as this is only triggerd by cell selection.

What are u trying to do anyway?

 

[unmarkedhelicopter]

Your post is not very clear.
If you are talking about buttons on a form then Clive is correct in that you are barking up the wrong tree.
If you are talking about a button drawn on a sheet then you are correct with shaperange but I don't know what you are trying to achieve.
The easiest way to check if you end up in code triggered by some event (This is the bit I don't get, as you should know how you got here) is to create a global variable somewhere and then in the trigger event assign that variable to a different value, which should thus point you back to the object used. I have an application that requires the user to pick two pictures (controls, objects or whatever could go in here) the first pick just sets a global, the second (cos the variable is no longer zero) does the comparison and then resets the variable for the next pick.

 

[Uncle Gizmo]

For clarity!

I have eighteen buttons which run code to highlight eighteen ranges. I have named the buttons on a number system one to eighteen, and the ranges are numbered one to eighteen also.

Currently each button runs separate code written eighteen times, to highlight the ranges.

If I could extract the number element from the caption, then I would only need one piece of code instead of 18! This is quite common practice in MS Access, as it saves a lot of work. However I cannot seem to find any way of extracting the caption from the command button.

The command button is not on the form it is on the spreadsheet.

the real question is, is the command button, (one placed on a spreadsheet) and object? And if it is, are its properties accessible to code?

I have attached a spreadsheet so you can see what I am trying to do.

 

[Uncle Gizmo]

And this is how it is used.

demo one

demo two

 

[unmarkedhelicopter]

Hmm !
No you can't do this with a shaperange object as there is nothing to test against. the best you could hope for would be a generic sub called by your 18 buttons each passing a range (or the string representing a range) to the sub
i.e.

sub button1
    call fhighlight("F4:F12")
end sub

Your sheet is I assume for you to do the sudoku manually with just a little help using highlights (haven't played with your code ) this will not help you do 4 by 4 squares (i.e. 16 element) or any of the smaller ones, 12, 9 (like yours) 8, 6, or the easiest 4.
In each case you have a maximum number of characters that each cell 'could' be, and you try to knock this down to 1. There are better ways to do this but if the cell contains more than 1 charachter you could have the font size small enough to show all possible numbers etc,
If you want code like this I can forward you some already written or are you just trying to solve this for yourself as a programming exercise ?

 

[Uncle Gizmo]

>>>> programming exercise ? <<<

Thanks for the offer of the code, I may very well take you up on it! However I don't think I should look at any just yet as it would spoil it as a programming exercise.

It did occur to me that I have the basis for constructing the code to do the Sudoku, and yes this would be an excellent exercise! However I don't know if I can afford the time at the moment.

I have done tic Tac toe , programming it myself, I wrote loads of lines of code, and then on the Internet I found a solution I think someone did it in about twenty lines! Very demoralizing!

So yes, it is very probably a future project, I want to develop my skills with object oriented techniques, so I will probably do it within VB.Net.

 

[unmarkedhelicopter]

Okay, but am I to understand that you actually want the code to solve the puzzle or just help you solve it, there's a big difference.

Caution may contain spoilers

I'm going on hols for a couple of weeks so I can't do anything short term myself

Manual

If you want highlights I'd put a number to represent square size, basic grid formatting and area determination would come from that. You 'could' have another cell that allows you to enter say 9 and have ALL the 9 cells highlighted. Other than that I think you'd need just 1 button for clearing, the rest could be done with selection.
I see why you wanted individual rows, columns and squares highlighted but why the first row of squares ?

Would you want possibles to be listed ?

Having said that, highlighting a single row, column or square doesn't help you very much. Multiple rows and columns can show you exclusion zones but ...

Solving by Code

At some point in your progress (you've probably already come across it if you do Sudoku manually) you'll find that 'some' puzzles need you to take a guess, from there on in ALL your numbers are just guesses until you actually reach a FULL solution. You need to keep track of this and be able to back up if it doesn't work out. You then take a different path.

Some puzzles are not only different sizes but also overlap in different ways also, you need to know where each puzzle ends.

Most everyone knows the exclusion methods for solving sudoku but it seems few know the 'other' technique. Say you have a square where you know 4 of the numbers (on say a 9 cell square) of the remaining cells based on the surrounding row and column interactions you know that cell 1 could be 1,2,8 cell 2- 2,3,8 cell 3-3,6,8 cell 4-2,8 and cell 5-2,3. Then cell 1 has to be 1 as none of the others can be, this is not easy to code !

Do you want to be able to solve 3D sudoku also i.e. a cube (say) with 9x9x9 values (3x3x3 squares) or even one of the BIG ones ?

These take time as I once solved a 3x3x3 square cube from just 1 value in about 13 mins (Excel VBA isn't that quick ! ) This is 27 interlocking and inter-dependent puzzles.

Also, it depends on whether you want to watch the puzzle being solved or just have it solved (it's at least a factor of 4 on the time taken).

My solutions (and just about every sudoku solving code set I've ever seen) use arrays to keep track of confirmed numbers, guess numbers, level of guess, possibles etc. (I'ts quite a while since I've done this ) so you need to be quite familiar with custom type arrays.

I 'may' have a play on my return with the manual side (not done anything with that before) but for a starter I'd suggest you stop using integers and use longs instead. Integers actually take longer to process than longs as there are a lot of internal conversions and checks on integers, there is a microsoft article on this, just do a search on the knowledge base.

Let us know if you need anymore info / pointers / suggestions / just plain spoilers