Microsoft stops working after trying one too many choices

[geecee]

It’s me again looking for more help. The attached programme consists of two worksheets, Sheet1 and COMPILER. Sheet1 contains a range of words in columns A-E and rows 1-4. In COMPILER the words can be chosen randomly and only once by clicking the appropriate cell. When the 20 words have been chosen a pop-up message box indicates "No more selections." If I click the cell again Microsoft stops working. Even if I save the programme when the 20 words have been chosen and try to run the macro again Microsoft stops working. Is there any way to prevent this?

Any help will be appreciated.

Thanks.

SELECTION TRACKER.xlsm

[HansV]

I can't reproduce the error, but why don't you check whether any selections are left at the beginning of the HELLO macro?

[Doc.AElstein]

Hi George.
When I do what you said click the cell again. after all cells have been selected, then Excel does not stop working…. It goes on for ever .. and ever … and ever … because this bit keeps going around in circles

Code: Select all

here:
    x = Int(lr * Rnd) + 1
    y = Int(lc * Rnd) + 1
    
    If Worksheets("Sheet1").Cells(x, y).Font.ColorIndex = 3 Then Worksheets("COMPILER").Cells(20, 1).Value = .Cells(20, 1).Value: GoTo here

That makes sense as Worksheets("Sheet1").Cells(x, y).Font.ColorIndex will always be 3 so it will keep going to here:

Something like Hans suggested should cure that I expect

Alan
_._________________
By the way, I am glad I looked at this. The following might help you as well as having done something I was trying for years to get at
It seems that if you do this for your entire range .._

Code: Select all

 Sub testMyRedOnes()
 Dim Var As Variant
 Var = Worksheets("Sheet1").Range("A1:E4").Font.ColorIndex
End Sub

_.. then if all your fonts are red then you get 3
_. What is interesting for me is that if not all the Fonts are red then it appears that 2 conditions are met
_..1) .Font.ColorIndex does not error
But
_.. 2) .Font.ColorIndex can’t get any value.
I believe that those two things happening at once are the conditions to have returned a Null
Low and behold you see that you get that in the code below.
I have been trying to find a code example to return me a Null, for some time. ( Don’t ask me why I wanted to do that – I just thought ..” well someone ought to “ :-) )

Code: Select all

 Sub MyRedTests()
' make all my cell fonts red
 Let Worksheets("Sheet1").Range("A1:A4").Font.ColorIndex = 3
 Let Worksheets("Sheet1").Range("B1:E4").Font.ColorIndex = 3
Dim Var As Variant
 Let Var = Worksheets("Sheet1").Range("A1:E4").Font.ColorIndex ' returns 3
 If IsNull(Var) Then MsgBox prompt:="You ""Nulled"" !!" ' you won't see this message
 
' make one of my cell fonts a different color
 Let Worksheets("Sheet1").Range("C3").Font.ColorIndex = 1
 Let Var = Worksheets("Sheet1").Range("A1:E4").Font.ColorIndex ' returns Null
 If IsNull(Var) Then MsgBox prompt:="You ""Nulled"" !!" ' you will see this message
End Sub 

' Ref Null stuff :


' http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA#post10159
' http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA/page2#post10174 

[geecee]

Thanks Hans and Alan for your replies.

Hans, I don’t think any selections were left at the beginning of the HELLO macro?

Alan, I ran your code as a separate programme and, whilst it worked, it’s a little bit above me.

Again. Thanks, and would you believe it I solved the problem with a simple "if/else/end if" which I have indicated between the ‘>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> lines in the code. I don’t know why I didn’t think of it before I posted my query.

Code: Select all

Sub HELLO()

    Dim x As Variant: Dim y As Variant
    
    '=======================================================
    '   SET COLUMN WIDTH AND ROW HEIGHT
    '=======================================================
    Worksheets("Sheet1").Range("A:F").ColumnWidth = 12
    Worksheets("Sheet1").Rows("1:4").RowHeight = 16.5
    Worksheets("COMPILER").Range("A:A").ColumnWidth = 16.71
    Worksheets("COMPILER").Range("B:L").ColumnWidth = 8.43
    Worksheets("COMPILER").Rows("1:1").RowHeight = 82.5
    Worksheets("COMPILER").Rows("2:100").RowHeight = 15
    
    '=======================================================
    '   SELECT START WORKSHEET
    '=======================================================
    With Worksheets("Sheet1")

    '=======================================================
    '   FIND LAST ROW CONTAINING DATA
    '=======================================================
     For Each x In Range("A1:A100").Cells
        lr = lr + 1
        If x = "" Then
            'x.Select
            Exit For
        End If
    Next
    Range("AE1") = lr - 1
    
    '=======================================================
    '   FIND LAST COLUMN CONTAINING DATA
    '=======================================================
    For Each y In Range("A1:AX1").Cells
        lc = lc + 1
        If y = "" Then
            'y.Select
            Exit For
        End If
    Next
    Range("AE2") = lc - 1
    
    '=======================================================
    '   DETERMINE MAXIMUM NUMBER OF SELECTIONS
    '=======================================================
    mns = ((lc - 1) * (lr - 1)): Range("AE3") = mns
    
    End With
    
    '=======================================================
    '   SELECT NEW WORKSHEET
    '=======================================================
    With Worksheets("COMPILER")
   
    '=======================================================
    '   CALL DATA FROM ANOTHER WORKSHEET AND RANDOMISE
    '=======================================================
    lr = Worksheets("Sheet1").Cells(1, 31)
    lc = Worksheets("Sheet1").Cells(2, 31)
    alldone = Worksheets("Sheet1").Cells(3, 31)
    
    'Initialize the random number generator
    '=> Randomize : add this before you call the Rnd function to obtain completely random values
    Randomize
    
here:
    x = Int(lr * Rnd) + 1
    y = Int(lc * Rnd) + 1

‘>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>    
    If Worksheets("COMPILER").Cells(20, 1).Value = alldone Then
        MsgBox "YES"
        Exit Sub
    Else
        If Worksheets("Sheet1").Cells(x, y).Font.ColorIndex = 3 Then Worksheets("COMPILER").Cells(20, 1).Value = .Cells(20, 1).Value: GoTo here
    End If
‘>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
    
    .Cells(1, 1) = "The word chosen is " & Worksheets("Sheet1").Cells(x, y) & "." & vbLf & "The count is " & Worksheets("COMPILER").Cells(20, 1).Value + 1
    .Cells(1, 1).HorizontalAlignment = xlCenterAcrossSelection
    .Cells(1, 1).VerticalAlignment = xlCenter
    .Cells(1, 1).Font.Name = "Verdana"
    .Cells(1, 1).Font.Bold = False
    .Cells(1, 1).Font.ColorIndex = 1
    .Cells(1, 1).Font.Size = 12
    .Cells(1, 1).Interior.ColorIndex = 34
    Worksheets("Sheet1").Cells(x, y).Font.ColorIndex = 3
        If Worksheets("Sheet1").Cells(x, y).Font.ColorIndex = 3 Then
            Worksheets("COMPILER").Cells(20, 1).Value = .Cells(20, 1).Value + 1
        End If
    
    If Worksheets("COMPILER").Cells(20, 1).Value = alldone Then MsgBox "No more selections.": Exit Sub
    
    End With

End Sub

[Doc.AElstein]

Hi George,
I expect Hans meant that , you should check to see if no selections are left . If no selections are left the you Exit the sub at the start, so it wont hit the place where it goes around in circles: Just to say that again – Your Excel did not stop working, - it went around in circles for ever. ( It may have crashed eventually )
You could have done a check at the start , such as
If Worksheets("Sheet1").Range("A1:E4").Font.ColorIndex = 3 Then Exit Sub
That code line checks to see if all the Fonts are red in the range "A1:E4" Worksheets "Sheet1" . If they are then you will exit the sub and not hit the place where it then started going around in circles
( By the way – do you use F8 step mode when developing code? – if you did F8 step mode on your original HELLO() code when all the Fonts were red, then you would have seen that the code goes around in circles at the point I showed.
( _3 . Use F8 step mode to “step through” code ---- http://www.eileenslounge.com/viewtopic. ... 47#p244647" onclick="window.open(this.href);return false; )
_.______________________________

.. ran your code ... it’s a little bit above me.

I would not worry too much about not understanding anything of mine as I make some of it up as I go along: My guess is as good as anyone’s sometimes as in VBA sometimes no one seems to know for sure…
This is what I was trying to say again….
For any range object VBA has a place where it stores the value of the color of all the cells in it. In other words it has a place to store , in your case, Range("A1:E4").Font.ColorIndex.
( It also has a place to store .Font.ColorIndex for each cell, but that is in a place somewhere else and separate to where it stores Range("A1:E4").Font.ColorIndex. )

Now imagine you, George, is VBA. You are only allowed to answer all questions given to you with a simple single short word.
If all your cells are red, and someone asks you _ Q = What color is all your cells - you have an easy answer _A = Red
If one cell is not red, and someone asks you _ Q = What color is all your cells - ……… Hmm …difficult …. You can’t answer Red because not all are red. You can’t say No, because the question can’t be answered with Yes or No. …
Sometimes in such a situation Excel will blow up / error or maybe tell you the error sort.
In this case the answer to that question is given as Null.
I think VBA has just been written such that in some cases where you ask a valid question , but it can’t give you an answer, then instead of blowing up , it gives Null.
If Range("A1:E4").Font.ColorIndex can’t be determined, then you still have the place in memory where that color index would be stored if it could be determined. But it can't be determined. You can't put anything in it untill all cells have the same font color. Often in such similar cases that might be shown as Empty or 0 or "" . In this case it is shown as Null
Or maybe it has something put in it when you can't determine a number to put in it ... it has a Null put in it - who knows - I don't - I told you - I am making it up as I go along https://www.youtube.com/watch?v=7FvoXJCrBhQ" onclick="window.open(this.href);return false; :-)
So if Range("A1:E4").Font.ColorIndex is Null, then you know that all your fonts in that range are not the same color. I suppose that might be useful to know sometimes
_._____________________
Both Null and Empty are Variant types. They are similar but different: I have sometimes heard the difference in them given as
Empty is not used ( yet ) , and
Null is used ( attempted ) but not filled with correct data
and / or
Null indicates that the Variant variable intentionally contains no valid data
This Last bit fits our situation nicely…. if not all cells have the same font color, then I intentionally don’t want to tell you what all my font colours are in that case, not in a single word anyway. :-)

Alan

Ref:
http://www.eileenslounge.com/viewtopic.php?f=30&t=2436" onclick="window.open(this.href);return false;
http://www.excelfox.com/forum/showthrea ... les-in-VBA" onclick="window.open(this.href);return false;

[geecee]

Alan,

Thanks for all that. I’ll print it out and study it.

I wasn’t really sure what Hans meant but what you say is probably right.

To your question ‘By the way – do you use F8 step mode when developing code?’, I can definitely answer No.
With regard to ( _3 . Use F8 step mode to “step through” code ---- viewtopic.php?f=27&t=31602&p=244647#p244647 ), it looks like more studying is in order.

I’ll get back into the Workbook and check out these points mentioned by you and Hans as I have to get it right. It is a shortened version of my project which could be, and I repeat could be, as large as 26 columns by 100 rows, but I doubt if I could find that many words which must be of nine characters in length.

Again, many thanks to you and Hans for the information.

[Doc.AElstein]

I think debug, F8 mode is one of those things that once you start using it in developing a code then you wonder how you ever got away without it. I doubt I could write any code without it as I make too many mistakes as I go along. So I start writing, then go through it in F8 to see if it is doing what it should, then write a bit more, check again in F8 ….etc..
If you do a quick youtube google on something like ..._
VBA F8 debug mode debugging youtube
_... then you’ll get loads of stuff explaining better than I can.
For example, towards the end of this WiseOwlTutorials Tutorials video , the bit about “ Break mode / Stepping Through a Subroutine” https://www.youtube.com/watch?v=DlqKfYT ... N5&index=3" onclick="window.open(this.href);return false; …Actually, like most of the WiseOwlTutorials Tutorials they are well worth a full watch, .. the chap that does all those wiseowl videos is an unusually clear , concise, very good teacher.
_.___________
I personally also use two screens usually. A large old Telly as my main screen with spreadsheet and all other stuff on it, and then I have the VB Editor window in front of me on the smaller lap Top screen.

Alan

[geecee]

Thanks for that Alan.