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?
View 12 Replies View RelatedI 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've 10 workbooks (which represent different areas around the factory) that populate a master workbook (belonging to HR).
We've now started to password protect the 10 workbooks. When i open the master workbook and click update - i get prompted to enter the passwords of the 10 workbooks
My question - is it possbile to automatically have the passwords entered so that the master can get updated?
What other code than that shown below do i need to make my message box work,
"Module Code"
Sub MyMacro()
Application .OnTime TimeValue("00:00:00"), "MyMacro", ["23:00:00],
[TimeValue "01:00:00"=True]
MsgBox(BoMLogDue, vbOKOnly, BoMLogDue) As VbMsgBoxStyle
End Sub
"Workbook Code"
Private Sub Workbook_Open()
Application.OnTime TimeValue("00:00:00"), "MyMacro", ["23:00:00],
[TimeValue "01:00:00"=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.
TABLE A :
A
B
C
D
[Code].....
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
End Sub
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."
View 5 Replies View RelatedTrying to have A1 in Sheet1 having three values 1,2,3 if A1 = 1 hide rows from 1:5 and 10:1500 in three Sheets2,3,4
View 8 Replies View RelatedI 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.
Check the attachment: Survey.xlsxβ
how to make a date automatically show up in the current year.
For example, if A1 = 6/2/2005, and I want B1 to equal that same month and day, but 2011... How can I do that?
I know =6/2/2005 + 365*6 would work, but the date in A1 will vary... so it has to be a way of showing ANY date with ANY year with the same month/day but current year.
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 want to make Excel show page breaks automatically when I open a new file. Please tell me how to do that. I tried changing the book.xlt and sheet.xlt files, but to no avail. I am using Excel 2003.
View 9 Replies View RelatedI 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.
[URL]....
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.
View 11 Replies View RelatedI 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??
View 3 Replies View RelatedI can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.
View 10 Replies View RelatedI am havng problems with the belwo macro code -
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 a column of data held in column B.
I am required to show the following.
If there is data in the cell then it is to be left. If there is no data in the cell then I would like to show the value 0.
I have tried using a circular reference, using the formula =IF(ISBLANK(B1),0,B1) and other similar formulas but they dont work as the formula overwrites the data in it.
do I need a macro?, or conditional formatting?
i use function countif "aa" in range A2:F2 COUNTIF(A2:E2,"aa")how i can do, if i do not need 0 to show when it not found "aa". i want it show blank
View 4 Replies View RelatedI 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.
how to set password for VBAProject?
i know to protect the spreadsheet
i want to password protect my coding in the VBA Project module ...
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