I have a workbook with particular worksheets that should never be deleted. If they are deleted by the user the workbook will become unusable.
The users need the freedom to edit data within the sheets (so protecting them is not an option), however, I need to restrict this freedom so that they cannot under any circumstance delete the sheet. Is there such code that I could use in the worksheet code to stop this?
I want to run a macro that puts information from one sheet into another. The destination sheet is protected so I want to unprotect the sheet, do the copy and pasting then reprotect the sheet. The sheet has a password and I can't work out how to put that into the macro. It works fine without a password.
I am going to use Excel sheets as computer exam forms. What I need to know is: Is there a way of protecting parts of an excel worksheet from alteration? I want a sheet that will accept answers in specific areas only, and will not accept entries or alterations in other areas.
I have a sheet that has some cells already protected by a password. I want another person to be able to make changes to the unprotected cells and then be able to push a button (macro) that will then unlock the sheet activesheet.unprotect ("mypassword"), change all the cells to "locked" and then have the protect sheet window pop open so that they can type in their own password and save it. Is that possible?
I need is for the Roster sheet, which has the exact same column and row headings as the Leave sheet, to read the data from the Leave sheet and change dynamically if the Leave sheet is changed. If there is data in any cell then I need to protect just that cell in the Roster sheet so that the user creating the staff roster is unable to edit it out.
I have created a plain cut and paste macro on open from Leave to Roster but this doesn't give me the dynamic linking, and doesnt help solve the problem of needing to check each and every cell in Roster against a corresponding cell in Leave, but also if its blank in Leave to allow the user to update that cell in the Roster sheet.
A1 is number of days available this sheet goes to a routing recepient from me who fills A2 and B2. C2 and D2 are calculated. A2 has starting date B2 has ending date C2 is days between these two dates D2 is number of days left i.e. A1-C2
I want to lock and protect cells A2,B2,C2 and D2 so that these cannot be changed when I route it back to get next set of dates to be filled by the same person.
I have a macro that duplicates a excel file into how ever many files I need... so If I need 150 copies of the the file, it will make it 150 times.
However, each file the first tab called "Clients" has information that I want protected so users can't alter it. I can't protect it before I run the duplication because the data is added by a group of people and I need it unprotected until they add the information.
What I am looking for is a macro that I can click and run, allows me to select multiple files, then it will automatically Protect Sheet 1 "Clients" on all the files I select so I don't have to open all 150 files after data is entered and Protect sheet 1 manually.
Im looking to export a sheet from excel to access. The sheet has the same tables as does the access database. I need the code for this because I dont want to have to import from access manually each time.plus my users only have permission to use the excel sheet, as I dont want them messing with the info. Any Ideas?
I used the xlveryhidden method to hide a worksheet that contains all of my validation lists for drop-down range names. Unfortunately when I try to access the sheet without unhiding it, I get a Microsoft Visual Basic Run-time error '1004': Select Method of Worksheet class failed
Is there a way to let VBA access the xlveryhidden sheet through a macro without unhiding the sheet?
I'm trying to loop thru the various sheets in a workbook but am having trouble accessing the current sheet name. what I have below does not work and I am stuck with how to access the current sheet name, interrogate it for what it is named, and skip processing if it is a particular name.
Private Sub dispnames()
Dim b As Integer Dim r As Integer Dim iSheetCount As Integer Dim iSheet As Integer Dim sh As Worksheet Dim wsht As Object
iSheetCount = ActiveWorkbook.Worksheets.Count For iSheet = 1 To iSheetCount
wsht = sh.Name If wsht = "summary" Then Goto skipit End If
Worksheets(iSheet).Activate r = Range("A" & Rows.Count).End(xlUp).Row Range("A2:" & "A" & r).Select Selection.Copy
Sheets("summary").Select b = Range("Z" & Rows.Count).End(xlUp).Row + 1 Range("Z" & b).Select ActiveSheet.Paste skipit: Next iSheet
I have a named range, "DDNames", on a master data input sheet and in that range are names of donors. In the same workbook I have sheets that provide a quarterly summary of each of the donors. I have named the sheets "Smry_Miller", as an example. Miller's name is in the named range, as well as others, for the format for each summary sheet is Smry_NAME.
I have a few things I want to do on each sheet so I am working on a FOR loop so I can make my code easily scale-able for when we get new donors or lose one. I get a Run-time error: 9 on my code and I'm just down right stumped.
VB: Sub MakingLoop() Dim arrAllDD As Variant Dim i As Long Dim varDDNum As Long varDDNum = Range("DDNames").Count
The debug highlights the line "Sheets("Smry_" & arrAllDD(i)).Range("G1") = arrAllDD(i)"
I need to populate data from Access table to sheet1 using Excel/vba. I want to find out the percentage of batches scanned for the date selected for each time slot. There are Time slots in the columns from Q to X. i.e 8:00,9:00,10:00,11:00.....etc.
so I need to find out percentage of batches scanned before the above time slots.. To count total number of batches , we will use count(BatchNo) function in the SQL query. Because of company restrictions, I can't attach the access database but to give u an idea how the database is stored, I copied the data in sheet2 of the attached workbook. We have to find percentage of batches scanned in sheet1.
I have multiple worksheets spread across multiple Excel files (1 worksheet per file).
All files are stored in the same folder, and all worksheets have the same column headers and structure. I need a block of code that will combine all of these worksheets into a single worksheet in a master Excel file. That is, the code needs to:
1. Open the first Excel file. 2. Copy the first worksheet's contents into the first worksheet of the master file, beginning at the next empty row it finds. 3. Close the Excel file, and move on to the next file. 4. Repeat.
So in the end, ten worksheets residing on ten different Excel files will be combined into a single worksheet in a single file. No breaks are needed between them, instead, the last row of a worksheet would be followed by the first row of the next one immediately below it. No aggregate functions involved, no sums, nothing like that (which is why I don't think I can use the Consolidate function in Excel).
I am Generating Excel file with Macro using my asp.net (c#) application.
I am able to generate Excel file in development environment, but in Production it gives following error: "Programmatic access to Visual Basic Project is not trusted Line: Microsoft Office Excel"
I did googling a bit and found that I have to open Excel file physically make few security related changes in macro as below.
1. Open the Office application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box. 2. On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access. 3. Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.
Can i do above changes at runtime (using some code)?
I have an Access table which has following fields:
ScanDate Number DataType Type Number Type1 Number Type2 Number BatchNo Number Cases Number Pages Number
Now I have a useform in Excel so that when the user selects the Date from the combobox then it should check for total batch numbers (Count(BatchNo)), total cases (Sum(Cases)),Total Pages(Sum(Pages)) where ScanDate= Date from the combobox group by Queue Number. The Queue Number consist of Type+Type1+Type2.
See attached the Excelsheet where the data should be populated to. WBCount.xls
I have a master spreadsheet that houses most of the information that remains in my control, and several other user spreadsheets that update cells from that master through links.
I am having some problems lately with users doubleclicking the locked linked cells in their workbooks, and excel then wanting to open and give the user access to my master spreadsheet. Is there a way to disable this feature? I would like it to not even acknowledge the action if that's even possible because it confuses them as to why they are getting a prompt to open another document.
Basically I am trying to work out the impact of a limited time offer (say 6 months) which offers customers a discount on their phone bill for a period of time (say 12 months).
I'm getting stuck on the formula (if there is one!?) which takes into account the fact the build up and then roll off of customers - i.e. in month 14 the customers that signed up in the first 2 months will no longer benefit from the discount. I have attached a spreadsheet which hopefully illustrates this a bit better!