I have protected a few hidden columnx in my worksheet and have sent it to my client who has the password.
He had a complain. Every time he wants to unhide the columns, he gets an alert to unprotect the workbook. The process of going to the Review tab and pressing the unprotect tab is what he finds annoying. My questions is the following; Is there any way that the password box immediately appears once he attempts to unhide the columns?
I have a password protected form in access (I know this is excel but the code is the same), and when i click on the button and the inputbox opens for my password, i don't want it to display what i am typing, instead i would prefer asterix's?
I have recently written some programming in a spreadsheet (1) which, on opening, opens up another spreadheet(2), ammends this spreadsheet(2), and then closes it before the user can start using the original one(1). This is done to monitor the usage. The information added into the atomatically opened spreadsheet(2) includes date and time of opening and the users id as well as adding 1 to counter cells. Both .xls files are located on the same server and I dont want people to catch on to the monitoring and if they do I dont want them to then go into this file(2) and remove their information.
If I password protect this file(2) it then promts to enter a password when it runs at the startup of the initial spreadsheet(1) which is no good as it would defeat the purpose if the user knew the password. Is it possible to add a few lines into the code (see below) after it initiates the opening of the usage file?
Private Sub workbook_open() Application. ScreenUpdating = False Workbooks.Open Filename:= _ "S:Newman CommonCATEGORY MANAGEMENT - RangesEPoS Usage.xls" Dim Counter As Integer Counter = Cells(2, 7) Counter = Counter + 1 Cells(2, 7) = Counter Cells(Rows.Count, "BV").End(xlUp).Offset(1, 0) = Now Cells(Rows.Count, "BW").End(xlUp).Offset(1, 0) = Application.UserName ActiveWorkbook.Save ActiveWindow.Close Application.ScreenUpdating = True End Sub
I have a problem with excel, let say i have excel file (named A) and i also have another excel file (named B), file "A" is database, and consist of around 14 columns. so when in B, i type 1234 then in B i will get all the data that related to 1234 that consist of 14 fields and it will be placed in different place and different order.
I would like to be able to use the before save event to hide some sheets before the save then after unhide some sheets. So that the user carrys on with the sheets they had before saving but when the document is reopened the correct sheets are hidden.
This is what I have so far but unfortunately when you click close and then save changes it runs the before save code and then goes around in circles, reasking the user if they want to save changes
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim ws As Worksheet
Cancel = True Application.EnableEvents = False Sheets("Protected Content").Visible = True For Each ws In Worksheets If ws. Name <> "Protected Content" Then ws.Visible = False Next ws Me.Save For Each ws In Worksheets ws.Visible = True Next ws Sheets("Protected Content").Visible = False Application.EnableEvents = True
When I call a series of subroutines from different worksheets, ScreenUpdating = False is not working.Here is my
Private Sub Worksheet_Change(ByVal Target As Range) With Application .Calculation = xlManual .EnableEvents = False End With If ActiveCell = "No" Then Call Sheet3.DisableTBs Call Sheet4.DisableTBs Call Sheet5.DisableTBs Call Sheet6.DisableTBs Call Sheet8.DisableTBs Call Sheet9.DisableTBs Call Sheet10.DisableTBs......................
does excel have a function or some kind of method where you can have it display the range of cells that you have selected? For example, if I highlight cells A5:G7 what would be the code for excel to display "A5:G7" in a message box? I need this to be dynamic, so the next time I select cells B3:T32, it needs to display "B3:T32."
I have some data validation drop down lists in excel, I can tab through all the lists but I have to press alt + down arrow to show the list, Is their a way it can be automatically shown as soon as I focus on the tab.
I need a macro where user choose a value from validation, automatically lock the other cells and should show an error message.
Here is what I am looking for. User will choose Yes or no from cell H15 validation. If user choose "NO" from the validation then all other cells has to be locked in the workbook automatically except Cell H15 and a msg box needs to appear stating: "Select Yes from the validation".
I have a spreadsheet with a master front page, which basically copies the contents of other cells in other sheets for it's own content. ie:-
the formula in cell E16 on the master sheet is ='Sheet 7'!$B$16 (Please note that cell B16 is actually a merged range of B16 to H16)
Everything works fine until such time as the orginal cell contains an amount of text which is deeper than the cell on the master page eg cell B16 on sheet 7 is 330 pixels deep while the cell E16 in the master sheet is only 16 pixels deep.
What I would like to happen is for row 16 in the master sheet to expand in depth by whatever is requried to automatically show the full content of B16 on sheet 7.
I wrote a small code to hide some columns if a certain cell is equal to a certain string.
The cell is actually a drop down list and when they select a certain one, I want it to hide 2 columns. So I wrote the code with sub name Action, but I want it to be running all the time. I tried to achieve this by writing the following code however it gave me error 438 for my 2nd line.
Sub Auto_Open() Range("A1").OnEntry = "Action" End Sub
Auto Merged Post Until 24 Hrs Passes;Oh, by the way error 438 states: Object doesn't support this property or method
I am looking to adapt a piece of code (originally created by Ger Plante) so that it autofilters multiple columns of a table. I have three data validated lists that need to search 3 different columns in the table and filter accordingly, but also show all if no hits are made (hence why Ger Plante's code) was perfect in most respects. I would ideally like to keep the code as a Worksheet_Change event, but can deal with it being run as a normal Macro via a button if this is necessary.
VB: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B1")) Is Nothing Then Range("A5:C5").AutoFilter Field:=1, Criteria1:=IIf(Trim(Range("B1").Text) = "", "<>", "=") & Range("B1").Text End If End Sub
What i would like to do is Hide Rows Based on Cell Value in Multiple Sheets & Multiple Columns and i need the macro to be fast
Sheet1 If the value in Range BE11:BE160 equals 1 dont hide the row If the value is 0 or then hide the row Sheet2 If the value in Range BE11:BE160 equals 1 dont hide the row if the value is 0 or then hide the row Sheet3 If the value in Range BE11:BE160 equals 1 dont hide the row if the value is 0 or then hide the row Sheet4 If the value in Range O1:O150 equals 1 dont hide the row if the value is 0 or then hide the row Sheet4 If the value in Range B1:B150 equals 1 dont hide the row if the value is 0 or then hide the row
I own a Hair Salon which collates its daily takings using a spreadsheet. A section of this takings sheet asks staff to enter which Products have been sold and what they where sold for (RRP), so that once all products have been entered, totals at the end of the day allowing the end -of-day "till-up" process to reconcile purchases and services rendered aginst cash and cheques received.
Unfortunately there is over 600 products to choose from and this evergroes as new products are introduced by manufacturers. Names are long and often very similar, and product price manual listings are slow to reference, therefore the customer has to wait while the staff find the right product and select the right price.
All of this is subject to human error. What i would like is simply to enter part of the product name into a Cell and the right product to be visibly selectable and the price automatically added to an adjacent cell.
When opening a new Excel file, it is asking me for a password to an old excel file that was password protected, which I have deleted from my computer. It is still asking for this password every time I open a new or existing file. When I hit cancel it lets me open the file.
I have a spreadsheet, which has certain worksheets that are password protected. I need to make changes to some of the formulas, and the VBA modules, however I cannot remember the password!! Is there a way of identifying the password??
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.
I have a sheet that is password protected. I have this code attached to a command button. It will unlock the worksheet, autofilter it, print it, unfilter it, and password protect the sheet again. However it is protecting it without a password. I need to have it protected with the password so that someone will not be able to just go to tools to unprotect the sheet.
Private Sub CommandButton1_Click() ActiveSheet.Unprotect "rainforest" Columns("O:O").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Selection.AutoFilter Field:=1 Selection.AutoFilter ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowSorting:=True, AllowFiltering:=True End Sub
I have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.
I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).
I have 2 different formulas that I need changed in a similar way.
The first formula is for cell AV11: =SUM(BI11,BP11,BW11,CD11,CK11,CR11,CY11,DF11,DM11,DT11,EA11)+10
Every cell starts off blank.
What I need is for cell AV11 to always start off blank until data is entered into one of the other cells. The problem is that since the sum always needs to be +10 only when data is entered in the other cells, I don't know how to keep 10 from showing in cell AV11 when no data is typed in the other cells.
The other formula is for cell CO39: =(CU8)+3
I pretty much need the same thing. If no data is entered in cell CU8, then I do not want cell CO39 to show the 3.
When using the a file with VBA based on a button is there any way to add a password to that file at the same time?
That way it can be opened by anyone?
The code below works fine but the user can open the file in fact anyone can once they press the command button I want to add a password so when the file is reopened it requires a password?
Is this some thing vba even does?
Private Sub CommandButton1_Click() Const PTHNAME As String = "C:Feedback" Dim FileNameWithDate As String, x As Integer
FileNameWithDate = Format(Date, "yyyymmdd")
x = 1
If Dir(PTHNAME & FileNameWithDate & ".xls") = "" Then ActiveWorkbook.SaveAs Filename:=PTHNAME & FileNameWithDate & ".xls" Else Do Until Dir(PTHNAME & FileNameWithDate & x & ".xls") = "" x = x + 1 Loop ActiveWorkbook.SaveAs Filename:=PTHNAME & FileNameWithDate & x & ".xls" End If SendMailtoBDTT SendMailtoPerson End Sub