creating if style formulas in VBA

[sahmed8251]

Hello everyone.

I need your help!

Firstly. ADMIN. Please update the title thread accordingly as I am not sure it best suits my actual question.

I am a novice at this. my knowledge of VBA is: I basically copy and paste codes I find on the internet, try it out and if it works, modify to suit!

my code currently is as follows

Sub SHOW01()
'unprotect workbook to allow VBA to work
ActiveWorkbook.Unprotect (Easy123$)
'declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("01 MANF")
Set ws2 = Worksheets("01 COMMS")
ws1.Visible = True
ws2.Visible = True

'go back to gen assy sheet
'reprotect workbook
Worksheets("GENERAL ASSY LIST").Activate
ActiveWorkbook.Protect (Easy123$), True, False
'select a certain cell within the sheet selected above
'Range("A1").Select

End Sub

What the code does it unprotects the workbook, unhides the sheets, goes back to first sheet, and then protects the workbook again.

I also have another set of VBA that does the opposite (hides the same worksheets).

Is there a way to combine the VBA so it checks whether it is hidden or not first and then either hide or unhide accordingly. Like an if formula style on an excel cell.

Hope this all made sense. My first time on here so please bare with me.

 

[Snakehips]

@sahmed8251 Welcome.

Try the below.

Sub SHOW01()
'unprotect workbook to allow VBA to work
ActiveWorkbook.Unprotect (Easy123$)
'declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("01 MANF")
Set ws2 = Worksheets("01 COMMS")

'Switch visiblility on sheets
ws1.Visible = Not (ws1.Visible)
ws2.Visible = Not (ws2.Visible)

'go back to gen assy sheet
'reprotect workbook
Worksheets("GENERAL ASSY LIST").Activate
ActiveWorkbook.Protect (Easy123$), True, False
'select a certain cell within the sheet selected above
'Range("A1").Select

End Sub

Hope that helps.

 

[sahmed8251]

that works! Thank you.

One thing I did notice though is that when it protects the workbook again, the protect workbook button on the review ribbon looks like this.

when you then click on that button, it unprotects the worksheet without the password. Have I done something in my VBA?

 

[sahmed8251]

that works! Thank you.

One thing I did notice though is that when it protects the workbook again, the protect workbook button on the review ribbon looks like this.

View attachment 42517
when you then click on that button, it unprotects the worksheet without the password. Have I done something in my VBA?

got it work! looks like I shouldn't have included the brackets in the password.

 

[sahmed8251]

also this link is a good help in understand if statements in VBA.

Using If...Then...Else statements (VBA)

 

[Snakehips]

Glad it helped.
Within vba, 'Select Case' can be a good way to deal with multiple IF type options.