How to delete specific columns in protected copy of a macro enabled excel?

thirstforvba

New Member
Joined
Oct 1, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Ok. This is what I am exactly looking for. I work on a complicated (for me) macro enabled excel tool which we consider as a master copy. It has several sheets. But not everything is shared with the users. So once we compile data and we run a copy of the sheet the current macro deletes few sheets and protects few columns in a specific sheet and gives us a copy of the sheet. This is how exactly it has to run everyday. All I had to do was click on 'Run protected copy' button and it would do it for me. I didn't need to worry about anything until now. I am now dealing with users in a new country. Here the requirement is different. We have to retain only 1 specific sheet, the sheet where we protect cells. I have modified the macro to do this by extrapolating the code that what was already there. I added the all the other sheet numbers. It seemed simple. Sample below.

'---------------------start preparing protected copy-----------------------------------------------------------

'--------------hide settings tab and remove aux tabs-------------
Sheet7.Visible = xlSheetVeryHidden
Sheet8.Delete
Sheet9.Delete
Sheet6.Delete
Sheet10.Delete
Sheet11.Delete
Sheet14.Delete
Sheet3.Delete
Sheet4.Delete
'-------------------------------------------

'------------Delete columns in the protected copy--------------

But along with this, I also have to delete few columns. Column 1 (A), Column 2 (B) and everything from column 43 - 92 (AQ to CN). Note, we deal with excel rows and columns in the form of numbers and not alphabets (if this makes any difference). I have added the heading (as you see above) but don't know how to proceed after this. I did go through few tutorials online, but I am bit perplexed of how to do this. The reason why I am worried is, regularly it is meant protect few of these columns that I want them deleted. This is connected to another sheet and the color of the column. There are 3 (orange, blue and yellow). Blue always stay protected and yellow and orange doesn't. See existing macro below. Will the below macro impact the codes for deleting columns? I am not even sure if I was able to articulate myself well. This is best to my ability. I am available to answer any questions you may have. Note, I cannot keep experimenting with this as macro restricts us from running only one copy every 24 hours. Can you please help?

'----------------lock first row and columns of the color specified in the Settings tab-------------------
For i = 1 To Sheet2.Columns(Columns.count).End(xlToLeft).Column
If Sheet2.Cells(1, i).Interior.ColorIndex = Sheet7.Cells(2, 13).Interior.ColorIndex Then
Sheet2.Columns(i).Locked = True
Else

Sheet2.Columns(i).Locked = False
End If
'------------hide exclusions column---------
If Sheet2.Cells(1, i).Value = "Exclusions" Then
Sheet2.Columns(i).Hidden = True
End If
'---------------------------------------------
Next i
Sheet2.Rows(1).Locked = True

'-------------------------------------------------------------------
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

thirstforvba

New Member
Joined
Oct 1, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
SO I crossed the time limit of editing the above message. I was thinking of application perspective. If I can hide the above noted columns with a password will also do. So when I receive updated files from users I can only unhide the columns and run against Master tracker. This is how I envision it now. Can you help with this new updated request? I prefer hiding them with password and not actually deleting them.
 

thirstforvba

New Member
Joined
Oct 1, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
I have to create a protected sheet from a master excel. But now, before sharing protected sheet with 3rd party user, I have to hide few columns [Column 1 (A), Column 2 (B) and everything from column 43 - 92 (AQ to CN)] in the generated protected sheet and this should be password enabled. that is people shouldn't be able to unhide columns. So, when I receive the file back from them with their updates, I should be able to unhide these sheets using the password. How can I do that ensuring above columns are password enabled hidden when protected sheet is generated? Thank you.
 

thirstforvba

New Member
Joined
Oct 1, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
I haven't received any answers so desperately managed to do the following:

I have created a code to hide a set of columns when protected copy of my master excel is created.
For i = 43 To 93
Columns(i).Hidden = True
Next i

I have also developed a code to create a protected copy of a master file ensuring they are saved in the same folder.

Dim destfile As String
destfile = auxpath & "\" & Sheet7.Cells(2, 6) & " " & LValue & " -copy"
Sheet2.Protect Password:=Sheet7.Cells(2, 12), AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Sheet2.Activate
If Dir(destfile & ".xlsm") <> "" Then
ThisWorkbook.SaveAs destfile & " v" & Sheet7.Cells(2, 1).Value

Else
ThisWorkbook.SaveAs destfile
End If

Now when I open the protected copy, anyone can unhide the columns.

What I need is to be able to unhide the columns in the protected copy only when some password is input. I cannot seem to get around this in a protected copy. Can you please help?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,962
Messages
5,767,350
Members
425,405
Latest member
jaskimo

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