creating if style formulas in VBA

sahmed8251

New Member
Joined
Jul 6, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,393
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@sahmed8251 Welcome.

Try the below.

VBA Code:
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.
 
Solution

sahmed8251

New Member
Joined
Jul 6, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.

1625829925543.png

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

sahmed8251

New Member
Joined
Jul 6, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,393
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Glad it helped.
Within vba, 'Select Case' can be a good way to deal with multiple IF type options.
 

Forum statistics

Threads
1,148,277
Messages
5,745,810
Members
423,979
Latest member
Nuzuki

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