improve an Excel Visual Basic code
Budget $10-30 USD
Job Description:
I currently work with a personal excel workbook and I want to be able to share this file with certain colleagues but there is certain information that I don't want them to be able to see.
I have a very simple macro that when executed asks for a password to hide certain sheets of my excel (3 sheets) while leaving the other ones open and protected, except 3 sheets that I want my colleagues to work on. This works fine.
BUT I WANT TO ADD to this same code a macro to hide and protect all the rows that in column "F" contains "Repartos" and "Facturas" only in my sheet named "transacciones".
The sheet "transacciones" is a very long database (currently working on row 33293 and counting, so I want this to be smooth when I run the macro.
There is a second macro that "unprotect" everything back to normal (this works fine as well, but I don't know if you should add something once editing the previous macro
Dim ws As Worksheet
Sub ProtectAll()
Dim S As Object
Dim pWord1 As String, pWord2 As String, ShtName As String
pWord1 = InputBox("Please Enter the password")
If pWord1 = "" Then Exit Sub
pWord2 = InputBox("Please re-enter the password")
ShtName = "Workbook as a whole"
If pWord2 = "" Then Exit Sub
'make certain passwords are identical
If InStr(1, pWord2, pWord1, 0) = 0 Or _
InStr(1, pWord1, pWord2, 0) = 0 Then
MsgBox "You entered different passwords. No action taken"
Exit Sub
End If
Sheets("Edo. de resultados").Select
[login to view URL] = False
Sheets("Unitario-Kgs").Select
[login to view URL] = False
Sheets("Distribución").Select
[login to view URL] = False
For Each ws In Worksheets
If [login to view URL] <> "Transacciones" And [login to view URL] <> "Inventarios" And [login to view URL] <> "Produccion" And [login to view URL] <> "Catalogo de cuentas" Then
[login to view URL] Password:=pWord1
[login to view URL] Structure:=True, Windows:=False, Password:=pWord1
Sheets("Transacciones").Select
End If
Next
Exit Sub
End Sub
Sub UnProtectAll()
Dim S As Object
Dim pWord3 As String, ShtName As String
pWord3 = InputBox("Please Enter the password")
If pWord3 = "" Then Exit Sub
ShtName = "Workbook as a whole"
For Each ws In Worksheets
On Error GoTo errorTrap1
[login to view URL] Password:=pWord3
[login to view URL] Password:=pWord3
Next
Sheets("Edo. de resultados").Visible = True
Sheets("Unitario-Kgs").Visible = True
Sheets("Distribución").Visible = True
Exit Sub
errorTrap1:
MsgBox "Sheets and Workbook could not be UnProtected - Password Incorrect"
Exit Sub
End Sub
* i can't share my excel file.
I often open this file on my iPad excel app but this does not work with macro enabled files, is there something you recommend to be able to see them there?
I'm not an expert. If there is a better solution that you suggest for my code in general i'm open to review it
thanks.
19 freelancers are bidding on average $33 for this job
Top 1% in Freelancer.com Hi, Greetings! ✅checked your project details: ✅Completed Time: In project deadline We have worked on 850 + Projects. I have 6 + years of the experience in same kind of projects. If you More
""""""""""""""""""""""""ADVANCE EXCEL VBA EXPERT"""""""""""""""""""""""""""""""""""""""" "Sir, EXCEL is my specialty and I can perform any complex task in EXCEL. I am an EXCEL VBA and CODING, AUTOMATION, TEMPLATE, REPO More
I understand that you are looking for a solution to share your personal Excel workbook with colleagues while protecting sensitive information. I can assure you that I have the necessary expertise to help you achieve th More
Hi, I can update your macro to hide and protect all the rows that in column "F" contains "Repartos" and "Facturas" only in sheet named "transacciones". Best regards, Remis
Dear Client, I understand that you are looking to optimize your VBA code to hide and protect certain sheets and rows in your personal Excel workbook. You want to share this workbook with certain colleagues but want to More
CERTIFIED ENGINEER, EXPERT IN ENGINEERING&SCIENCE, Dear Client, I have Keenly gone through your project requirements as given in your project description. I gladly inform you that i am in possession of all clearly sta More
Hello Ciro I. This is Neveen from USA I have reviewed your complete description for improve an Excel Visual Basic code I would love to design it for you and surely reach out your expectations. I have 7+ years of exper More
Hi , ?? I read your project details well I sure I can do it, i have six years experience in excel & VBAField ,I had worked on many projects similar to your project, i can send you the samples from my work,I will start More
Hi, I have 17+ years of IT experience, out of which 12+ years are in Capacity Management in multiple companies. I work with huge amount data all the time. I am expert in Excel and Excel Macros and have automated many t More
