struggling with case statements

[swaink]

Hi All

I am rather new to the use of CASE statements and have found myself in need of assistance please.

I have put together a spreadsheet which has many individual macros to perform, I then added a progress bar which works well except I have one macro which runs for some considerable time fetching info.

I introduced some additional lines to that macro to calculate what stage it was at, this works ok but I am now trying to streamline the code to improve on it using CASE

I use the calulated figures to update the progress bar for example :-

If lngProcessingRow = mycount1 Then
    PctDone = 50
    With UserForm1
            .Frameprogress.Caption = Format(PctDone, "0")
            .Labelprogress.Width = PctDone * 3 
            .Label1.Caption = "Off we go time for a story"
    End With
    '   The DoEvents statement is responsible for the form updating
        DoEvents
End If

I have 10 mycounts plus the lines above so its quite large and I am looking to use CASE to simplify the whole thing but I can't get it to work.

My attempt at using CASE looks like this below:-

mycase = lngProcessingRow
Select Case mycase
   
   Case Is = mycount1 'this would represent a value say row 10'
    With UserForm1
         PctDone = 50 ' This represents the percentage overall that has been completed'
            .Labelprogress.Width = PctDone * 3
            .Label1.Caption = "Off we go time for a story"
            DoEvents
   Case Is = mycount2 'this would represent a value say row 20'
    With UserForm1
        PctDone = 55
            .Labelprogress.Width = PctDone * 3
            .Label1.Caption = "Or you could have time for a coffee"
            DoEvents
   Case Is = mycount3 etc up to case 10

Any and al help would be appreciated thank you

Regards

Kevin

 

[Von Pookie]

Looking at the code you have posted, I do see that you aren't adding the End With statements. If you are going to write the code in that way, you will have to add the closing statements before the next Case statement.

Case Is = mycount1 'this would represent a value say row 10'
    With UserForm1
         PctDone = 50 ' This represents the percentage overall that has been completed'
            .Labelprogress.Width = PctDone * 3
            .Label1.Caption = "Off we go time for a story"
            DoEvents
    End With 'the code for this case ends here
   Case Is = mycount2 'this would represent a value say row 20'

An alternate way to do things is to just use the Case statements to set variables that you then apply to the main object (in this case, it looks like that would be your userform) afterwards. For example:

Dim lngProcessingRow As Long
Dim PctDone As Integer, strCaption As String

Select Case lngProcessingRow
    Case Is <= 10 'this would represent a value say row 10'
        PctDone = 50 ' This represents the percentage overall that has been completed'
        strCaption = "Off we go time for a story"
    Case Is <= 20 'this would represent a value say row 20'
        PctDone = 55
        strCaption = "Or you could have time for a coffee"
    Case Is <= 30
        ' ...
End Select
With UserForm1
    .Frameprogress.Caption = Format(PctDone, "0")
    .Labelprogress.Width = PctDone * 3
    .Label1.Caption = strCaption
End With
'   The DoEvents statement is responsible for the form updating
DoEvents

(Please note that code is untested)