Change/Amend VBA Code To Run Fast

[hrayani]

Hello Friends,

I have a set of data which contains shipment records. I have to hide the rows where Column O
shows the status of the Shipment.

I am using the below mentioned code to hide rows based on Column O

Private Sub CommandButton1_Click()

For a = 3 To 500

If Worksheets("RO").Cells(a, 15).Value = "Shipped" Then
Worksheets("RO").Rows(a).Hidden = False
End If
If Worksheets("RO").Cells(a, 15).Value = "In Progress" Then
Worksheets("RO").Rows(a).Hidden = True
End If
Next

End Sub

Private Sub CommandButton2_Click()

For a = 3 To 500

If Worksheets("RO").Cells(a, 15).Value = "Shipped" Then
Worksheets("RO").Rows(a).Hidden = True
End If
If Worksheets("RO").Cells(a, 15).Value = "In Progress" Then
Worksheets("RO").Rows(a).Hidden = False
End If
Next

End Sub

Private Sub CommandButton3_Click()

For a = 3 To 500

If Worksheets("RO").Cells(a, 15).Value = "Shipped" Then
Worksheets("RO").Rows(a).Hidden = False
End If
If Worksheets("RO").Cells(a, 15).Value = "In Progress" Then
Worksheets("RO").Rows(a).Hidden = False
End If
Next

End Sub

Is there any other way out (code) that can provide the desired result a bit fast or can we
change the code to make it work fast

Any help would be appreciated

Regards,

Humayun

 

[hrayani]

Thanks Peter

 

[hrayani]

Hello Peter,

The code does not work when the sheet is protected/locked......

Any Idea ??

 

[Peter_SSs]

Yes, unprotect the sheet near the start of the code and re-protect the sheet at the end of the code.
To get the code to do that, start the macro recorder and unprotect the sheet, stop the recorder & look at the code. Same for re-protecting.

 

[hrayani]

Hi Peter,

Can u pls tell me how to assign these to the form control command button....

Right now i am using the ActiveX command button but the problem is that the ActiveX button is not working on the other PC.
I searched on the web to find a solution. Well there were some but the problem remains.

So i decided to assign all the macros which i have - to be linked with the Form Control Command Button... But i am unable to assign your code to the Form Control Command Button..

For the rest macros what i did is right clicked the Button and assigned the macros which i was able to select from the list.


These are the ones which at present are linked with the ActiveX command button

Code:Private Sub CommandButton1_Click()
Show_Hide bHideShipped:=False, bHideInProgress:=True
End Sub

Private Sub CommandButton2_Click()
Show_Hide bHideShipped:=True, bHideInProgress:=False
End Sub

Private Sub CommandButton3_Click()
Show_Hide bHideShipped:=False, bHideInProgress:=False End Sub

Kindly giude me how to do it....

Regards,

Humayun

 

[Peter_SSs]

These are the ones which at present are linked with the ActiveX command button

Private Sub CommandButton1_Click()
Show_Hide bHideShipped:=False, bHideInProgress:=True
End Sub

Private Sub CommandButton2_Click()
Show_Hide bHideShipped:=True, bHideInProgress:=False
End Sub

Private Sub CommandButton3_Click()
Show_Hide bHideShipped:=False, bHideInProgress:=False End Sub

Move those a standard module & remove 'Private' from the start of each one.
(Might also be a good idea to re-name them to something more meaningful)
You should then be able to right-click your Forms Control Command Buttons and assign the correct one of these macros to each.

 

[hrayani]

Move those a standard module & remove 'Private' from the start of each one.
(Might also be a good idea to re-name them to something more meaningful)
You should then be able to right-click your Forms Control Command Buttons and assign the correct one of these macros to each.

Thanks. I will do this & also what u told me in post # 33. Will give feedback.

 

[hrayani]

Hello Peter,

This part of the code is solved. I did as per u told me

Move those a standard module & remove 'Private' from the start of each one.
(Might also be a good idea to re-name them to something more meaningful)
You should then be able to right-click your Forms Control Command Buttons and assign the correct one of these macros to each.


Regarding The protect and unprotect sheet issue.

Yes, unprotect the sheet near the start of the code and re-protect the sheet at the end of the code.
To get the code to do that, start the macro recorder and unprotect the sheet, stop the recorder & look at the code. Same for re-protecting.

i did the same and got these recordings

Sub unprotect()
ActiveSheet.unprotect
End Sub

Sub protect()
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True
End Sub

Now when i protect/unprotect the sheet i entered a password - whereas the code does not show anything like that.
I want the Sheet to be password protected.

Any Idea what to do ??

Regards,

Humayun

 

[Peter_SSs]

Edit the password to whatever you have used.

ActiveSheet.Unprotect Password:="abc"

'Other code here

ActiveSheet.Protect Password:="abc", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

 

[hrayani]

Thanks a lot Peter.... Everything is working just PERFECT

 

[Peter_SSs]