struggling with case statements

swaink

Active Member
Joined
Feb 15, 2002
Messages
432
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 :-

Code:
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:-
Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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.
Rich (BB code):
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:
Rich (BB code):
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,841
Messages
5,598,390
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top