Can't exit while loop

[ccordner]

Good Evening

I have a slight problem with the code below. I want to check first that the cell selected is within a given range of columns (7 to 12). If not, I want to exit the while loop. I put in the msgboxes to try and work out why it was doing so. If I selected a cell in column 5, it carried on regardless. For information, the second valid=false seems to behave.

Any ideas?

Incidentally, the last line I am also struggling with. I want to refresh the combo box after this function has been called, but it keeps saying object required.

Thanks
Chris

    While Valid = True
    
        'Check if column is valid
        If CellCount.Column < 7 Or CellCount.Column > 12 Then
        
        
            MsgBox ("Confirmed out of limits")
            Valid = False
            
            MsgBox ("yet got beyond valid=false")
        
        End If
        
        MsgBox (CellCount.Column)
        
    
        'Work out what row we're in
        If Cells(CellCount.row, 5) = "On" Then
            RowNumber = CellCount.row + 4
            Else
            If Cells(CellCount.row, 5) = "Off" Then
                RowNumber = CellCount.row + 3
                Else
                If Cells(CellCount.row, 5) = "Worked" Then
                    RowNumber = CellCount.row + 2
                    Else
                    If Cells(CellCount.row, 5) = "Notes" Then
                        RowNumber = CellCount.row + 1
                        Else
                        If Cells(CellCount.row - 1, 5) = "Notes" Then
                            RowNumber = CellCount.row
                            Else
                            Valid = False
                        End If
                    End If
                End If
            End If
        End If
        
        If InStr(Cells(RowNumber - 1, CellCount.Column), "RD") > 0 Then
            Cells(RowNumber - 4, CellCount.Column) = ""
            Else
            Cells(RowNumber - 4, CellCount.Column) = ""
            Cells(RowNumber, CellCount.Column) = Sheets("SAP Codes").Cells(RowCount, 3) & Chr(10) & "(" & SAPCode & ")"
        End If
        
        Valid = False
    
    Wend
Next
    
'MyRibbon.Invalidate ("cboSAPCodes")

 

[craig.penny]

You might step through this to see how it's working. I think the While Wend structure isn't doing what you want. Even though Valid gets set to False (when the column is outside your desired range) it doesn't exit the loop until it gets all the back to the top

While Valid = True

Which is when it exits the loop.

You could try a Do While Loop and use something like

If CellCount.Column < 7 Or CellCount.Column > 12 Then Exit Do

As for your second issue, is this the line that's causing a problem:

MyRibbon.Invalidate ("cboSAPCodes")

?

 

[ccordner]

Thanks, that works much better, even if I'm not entirely sure why. I always thought that with a while loop, as soon as the condition changed it ended.

Never mind.

And yes, that was my second problem. I have a workbook of many worksheets, so I want a combobox in the ribbon. It works fine, when you select the item it performs the appropriate actions (or at least it does now) but if you want to do the same action on a different sheet, there's no way of resetting the combobox.

I've looked through every help-page I can find (help pages about the ribbon tend to be sparse. Those that aren't too technical or about a specific problem are even rarer).

The best I can find is the suggestion of the code:

Dim cboSAPCodes As IRibbonControl
Public Sub InitialiseRibbon(ribbon As IRibbonUI)
    MyRibbon = IRibbonUI
End Sub

At the top of the module, with

MyRibbon.Invalidate ("cboSAPCodes")

at the bottom. I know that the 'InitialiseRibbon' is being called on loading, because I got the code wrong once and it went to debug.

But for some reason, it gets to the 'InvalidateControl' line it says "Object required"

Any help MUCH appreciated!

Chris

 

[craig.penny]

I don't think I'm going to be much help with the Ribbon issue...

If I had to guess I'd say maybe something hasn't been created yet with regards to "cboSAPCodes". Maybe you could put in another line like

If IsObject(cboSAPCodes) then MyRibbon.Invalidate ("cboSAPCodes")

I should caveat this with not only is this not tested but it is also a complete guess