i currently have created a macro for copy a selection of cells from one work book to another. The file were i am copying the cells from is Track_&_Trend_00.xls. In this there is a password to open and modify. The sheet that the cells are being pasted to is TRACK & TREND.xls. There is no password on this. My macro runs fine only that midway through it asks for the password to open and the password to modify for the sheet Track_&_Trend_00.xls.
I wonder ifany one would know about entering input lines of code below to automatically put these passwords in and not having the two password dialog boxes pop up midway through running the macro.
Once I open a new workbook, how do I make it the active workbook to have a function make changes to it? Problem I am having is even after it is open, the function will only make changes to the workbook containing the Macro (Thisworkbook)....
I have the following code that Ger Plante very kindly helped me with which, depending on whether there is an 'x' by someone's name in a list, creates a new workbook, copies some information to it and saves it before moving on to the next 'x'. Loop Through Rows & Copy Each Row To New Workbook
For lLoop = 2 To 251 'first row of data to last row. If ws1.Cells(lLoop, 4).Value = "x" Then '4 = Column D ws1.Activate ws1.Range("e" & lLoop & ":g" & lLoop).Copy ws1.Range("B1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Rng1.Copy Workbooks.Add ActiveSheet.Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ActiveSheet.Range("A1").Select ActiveSheet.SaveAs varPath & "Student Data Files" & ActiveSheet.Range("B1") & ".xls" ActiveWorkbook.Close
Else End If
Next lLoop
how I can modify the code such that any cells in the range "b1:b504" in Sheet1 of the the new workbook can't be selected or edited without a password....I have tried unsuccessfully using Protect but am not sure how to get vba to set it to specific cells and determine exactly what is allowed in those cells.
I have a piece of code that prompts me to choose which column to be filled bottom up, I would like to modify this and add it in as a macro so that it fills columns H and columns I in a bottom-up fashion
Range("G7").Select Selection.Copy Workbooks.Open Filename:= _ "Q:PublicPAYMENTS Q&RREPORTSSuspense Activity BUSINESS2008 - Suspense BUSINESS - Activity Reports2008 Avon.xls" Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.Close End Sub
When i open the workbook it requires me to insert a password, i tried to type this but excel will not follow keystrokes, i then inserted the password in cell G7 which i then copy and paste, which you can see the copy at the beginning of the macro.
Is there a way where you can get it to insert the password so i can open the workbook in a macro.
What im trying to do is save 26 suspense spreadsheets by just clicking on a macro.
adds the file name into column A if it could also hyperlink it to that file. 2nd, Change it so it doesn't start a new workbook and worksheet. I would like it to just run in the Workbook it is in and each time the macro runs or the workbook opens it updates any info that has changed.
having trouble editing the code to allow for dynamic ranges. I have tried to research online, but am having a hard time with it. I think I would understand better if I could see a practical example.
Here is the sample macro I recorded in Excel that I am working with:
Sub Macro1() ' ' Macro1 Macro ' ' Cells.Select ActiveSheet.Range("$A$1:$C$87").RemoveDuplicates Columns:=Array(1, 2, 3), _ Header:=xlNo End Sub
How can I edit this to allow for dynamic rows and columns?
I've got a macro function which calculates the area of a polygon freeform using the 'shoelace'-methode. I would like to excute this function after i leave the shape editing mode.
For example: I have a square, freeform shape. By right-mouseclick I can edit the shape nodes. If i drag one of the nodes of the square to another position and click outside of the shape, the new shape is updated. Right, on that moment i would like to trigger my macro to recalculate the area.
Is there an event in excel to aim for, like the Worksheet.change-event or something similar?
i have a list of about 20,000 phone numbers that I need to edit.
all start 01 123456 01 123457 01 123458 etc
I am trying to create a macro that will change them to
+001123456 +001123457 +001123458 Etc
However, no matter what I try it always comes out as
+001123456 +001123456 +001123456
F2 Copy and paste is going to take me forever, and I am sure that there must be a quicker way to do this.
this is my first time creating macros so am completly lost. I want to keep the text that is in the cell that I am editing, just add the country code and delete the space.
I found some username and password login code that I am editing for my needs, but I am having some trouble with it. I keep getting run time error '448': Named argument not found on the following line:
I have a code to verify the user name and password, what I want is counting the times the user enters wrong password and show a msgbox when he enters three continuous wrong passwords:
I have created the following lines which are part of a long code, the code is actually running without errors, but it is not showing the msgbox,
I have a sheet which i made ages ago and cant remember my pw. I can goto the alt, F11 select the macro and run it which i have set from all sheets to hide, and i can change the wording on the sheets but i cant open the code.
Does this mean i will have to forget about it unless i remember the pw?
I have an add-in which I want to share with others, but I want to protect the code from modification.
I know how to apply a password to a VBA project (Tools -> Project Properties -> Protection) but the problem is this: when a colleague installs the add-in, the subroutines within the add-in are not available in the Macros list to be added to the ribbon (in the Customize Ribbon window). The subroutines only appear when I remove the password.
I`m struggling to insert password protection to code.If Yes is selected from the messagebox then a messagebox opens and asks for a password.
Code: Sub DeleteRanges()
If MsgBox("Are you sure you want to delete data ?", vbYesNoCancel + vbExclamation, "Delete data") = vbYes Then With Sheets("Score") For i = 3 To 273 Step 10 .Range("D" & i).Resize(7, 11).ClearContents Next End With End If
I have an excel spread sheet with say 20 or so sheets. Sheet 20 known as "Rates" is hidden. What I would like to do is when the user presses a command button say on sheet 1 he is asked for a password which will unhide the Rates sheet . If he gets it wrong a message pops up say incorrect please try again or have an exit button to cancel.
I have a workbook whose worksheets I want to protect on open, and also want to unprotect by way of a module. The protect code is:
Code: Private Sub Workbook_Open() Sheets("Cornealrings").Protect Password:="elohim" Sheets("Ferrararings").Protect Password:="elohim" Sheets("Kera Rings").Protect Password:="elohim" Sheets("IS CALCULATOR").Protect Password:="elohim" Sheets("IS Manager").Protect Password:="elohim" End Sub
The unprotect code is:
Code: Sub Unprotect() Sheets("Cornealrings").Unprotect Password:="elohim" Sheets("Ferrararings").Unprotect Password:="elohim" Sheets("Kera Rings").Unprotect Password:="elohim" Sheets("IS CALCULATOR").Unprotect Password:="elohim" Sheets("IS Manager").Unprotect Password:="elohim" End Sub
The code stops at the first line of the unprotect event, with the message that the password is not correct. If I try to manually unprotect, the password is accepted.
I am trying to add a command button that will set a custom view, password protect all worksheets, and finally password protect the entire workbook structure. The code I am using will correctly set the view and add the password to the worksheets, but it will not apply the password to the workbook structure. What do I need to add?
I have created a workbook that mostly works with VBA codes. Within the code I have ensured that certain worksheets remain hidden. However, I have also written a macro to access what I called the ADMIN worksheet, which has several commands enabling updates and troubleshooting. Long story short, I would ideally like to prevent users from accessing that worksheet. I have already created a prompt (Msgbox) asking the user if he/she wishes to proceed with the macro but I would prefer being able to have a password prompt to run the macro.
I have a code that opens a workbook and saves it under one file name and then saves it under 2 different file names, but it keeps on saving them as password protected and I can't work out why. Would it be something to do with the code or something else? The following is the code I am using:
I have been using this code and just noticed that it resets all of my options buttons to false when I exit and then re-enter (activate) the sheet with the option buttons.
I see where this is going on, but don't know how to correct it. I only want the option buttons changed to false if
The Sol named range is something other than "Primary Vendor". It seems to call the macro ClearOB whenener the sheet is activated.
Private Sub Worksheet_Activate() If Range("Sol").Value = "Primary Vendor" Then For Each OB In ActiveSheet.OptionButtons OB.Enabled = True Next OB ClearOB ActiveSheet.ScrollArea = "A1:K58"
Am trying to find the VBA code to "ask a user for the password to unhide multiple sheets/tabs"
I have got the script for unhiding all sheets which is:
Sub Sheets_Unhide() Dim sh As Worksheet For Each sh In Worksheets sh.Visible = True Next End Sub
What i would like though is for it to prompt with a password first before the code is executed for unlocking 50 odd sheets.. I know its not 100% fool proof, but would do for what i need!
I have a protected sheet so specific people can only see certain things. I would like code to Unhide a tab called "Estimating" and unhide Rows 2 through 9 and rows 24, 26 and 27.
I have a 2010 excel workbook with several locked worksheet (to which I manage the PW). I and another staff member manage different section of the macro but the other staff member doesn't have access to the locked areas.
Is there a way I can encrypt the password within VBA so it's not visible to the other staff member?
Locking the VBAProject doesn't work as the other staff member has to be able to edit his VBA section.
Many staff run the macro (via a button) and don't need to access the Macro and don't have access to the protected sheets.
I understand excel isn't ideal with PW protection for people wanting to bypass the protection and this isn't an issue.
I've used VBA to make a picture appear when all the correct answers have been entered and it all works well. However, not to be too mean to 15 year olds, many of my students can be devious little feckers, and I want to hide and password protect the VBA code so that they can't just change the pictures visible section to true. I can password protect the workbook and worksheet, but not the VBA.
I'm entering the Visual Basic editor and I can see my simple script. I then click Tools - VBproject properties - protection. I'm clicking the "Lock Project for Viewing" box then filling in the password and confirming the password and clicking OK. But I can still see and edit my script, despite protecting the sheet and workbook.
I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
I have an excel spreadsheet that has password to open it as well a read only prompt. What I would like to happen is after the user has typed in the password, clicked ok, and then clicked either "yes" or "no" for read only or not that a specific named macro immediately is run.