I am creating a spreadsheet for work - I have a column that is all the formulas projected sales minus actual sales.
I need to lock this column so that no one BUT MYSELF can change the codes. In other words "idiot proof" the worksheet. there are only 18 sections that I want anyone else to be able to type in.
is there a way to do this without adding users? half the ppl have personal emails and half have company emails.
I wish to send a workbook but need to ensure that this workbook stays on the computer it is destined for. I understand that for ultimate security excel may not be the best option, however im using excel and with that need to look at the best options available to me.
My thoughts were along the lines of sending a simple application for better words in excel, and having the user fill in a few details, this would trigger a events macro recording A the machine id ( which i have forgotten how to do any any help would be great with that ) or B the user ID to a hidden cell, then on receiving this back be able to use that as a key in the application before sending it out.
Then if the user was to pass this to another person it would not work as the code would not match. Does this seem like a best approach or is there a better way that a similar effect could be had?
I have a workbook with 6 tabs. The workbook is designed to be shared but I want to password protect the last 4 sheets so that they can only be accessed by certain people. The people who have access need to be able to edit the sheet.
View 8 Replies View RelatedI am looking at a way to create a dsitributable excel spreadsheet (.xlsx), which contains non-sensitive, yet restrictied organisational information segmented by regional department for NCSA, EMEA for APAC respectively.
By design, all data has been collected in a datatable on a password protected "data" sheet - and - presented on an "executive summary" sheet. T
The "executive summary" sheet contains a privottable with select "multiple options" to allow for aggregated dataviews on executive level. However, on a regional department level, the users must only see their own regions, hence the filtering mechanims on the pivottable should be restricted.
Currently, the restrictions on the pivot tables are made in VBA, using the following script. However, it still allows people to change the "multiple options" filter in the pivot. How can I restrcit this option
Sub RestrictPivotTable()
Dim pf As PivotField
With ActiveSheet.PivotTables(1)
.EnableWizard = False
.EnableDrilldown = True
.EnableFieldList = True
.EnableFieldDialog = False
.PivotCache.EnableRefresh = False
For Each pf In .PageFields
[code].....
As a sidenote; the above design requires that a spreadsheet is compiled for each region each month. Ideally, the ability of filtering, could be password restricted - or - filteren. So only one selection would be presented for the NCSA, EMEA and APAC respectively, while Executives could view across one-or-many regions? However, as Pivot tables are used by design, I cannot see how this could efficiently be achieved?
A Macro needed which will be assigned to a button,say “Check” and when the same will be clicked all the sheets will be say protected an users cannot modify any data. Another button will be there which will be assigned macro & when clicked will ask for a password to be known by only some administratives say departmental heads only and they can modify the data.Users after clicking the “Check” button,cannot modify the data.
I m just looking out for some code where i can restrict the user from changing or inserting a value in a particular column in excel
View 9 Replies View RelatedI want to restrict user from entering alphabets like I,O,Q and S in a cell. Can we do this using data validation or any formula because I don't wan't to use drop-down list or refer to values in other cells either.
View 3 Replies View RelatedI'm using Excel 2010, and I need to restrict the value the user can enter into a cell (E9).
In cell E3 is the screen width (pixels). eg 6024
In cell E5 is the preferred width of a window. eg 450
The user, in cell E9, enters an x coordinate for which they prefer the top left corner of the window whose width is specified in E5.
If the value that the user enters in E9, added to the width entered in E5, exceeds the value of E3, (if E9+E5 > E3) then the value should be disregarded (window will be off right of screen) and the user re-enter.
I'm not familiar with the use of data validation, so I'm uncertain as to how to use it in this circumstance.
I have a userform (Login), which allows you to login by entering your username, password and by selecting your role.
When you enter the username, password and role and click on submit, it shows another userform (Activities).
Now what I want is, when i enter username, password and select the role as team manager and click on submit, it should show the userform - "Manager_Login". For all other roles, it should show the userform - "Activities".
The usernames, passwords and roles are on the sheet - "User Access".
What's the easiest/best way to add a menu containing menu items so that a user can easily access custom vba-functionality?
View 2 Replies View RelatedI'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
How to restrict copy paste for certain cells in excel?
For Example : Range ("A1:A20) and Range ("C5:C20")
I have this code working fine. It applies conditional formating to two rows ((K3:K65) and (J3:J65)) and checks when a value changes in row B to reaplpy the formatting. I'd like to had add more columns to it. It would have apply the same conditonal formatting on rows ((O3:O65) and (P3:P65)) but when a value changes in row (N3:N65)
So basically, I want to keep the first working part of the code and have it to check at more rows with a different reference for the conditionnal formatting.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rngJ As Range
If Target.Count > 1 Then Exit Sub
Set rng = Union(Range("B3:B65"), Range("J3:J65"))
If Intersect(Target, rng) Is Nothing Then Exit Sub
Set rngJ = Range("K" & Target.Row)
Select Case rngJ.Value
Case ""
rngJ.Interior.ColorIndex = xlNone
rngJ.Offset(0, -1).Interior.ColorIndex = xlNone
Case Is >= 20.............................
I am trying to make a macro run automatically based on info entered in a cell. The problem is I need this code on at least three separate sheets in the work book. It doesent work when I try to use the same basic code. The code I am using is below.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lLastRow As Long
Dim rgArea As Range, rgCell As Range
Dim COL_B As Integer
COL_B = 2
Dim COL_X As Integer
COL_X = 24
Dim ROW_FIRST As Long
ROW_FIRST = 5
' Find the last row of date in the "Approved" worksheet
lLastRow = FindLastRow(SZ_WS_APPROVED, COL_B)
' Set a reference to the changed cells in column X
Set Target = Intersect(Target, Range(Cells(ROW_FIRST, COL_X), Cells(lLastRow, COL_X))).........................
We have a spreadsheet on a server location with macros. Different users will access the spreadsheet from one pc on different shifts. Is there a way to set the area as a Trust Location for all users on the PC or enable the macros in the spreadsheet to execute for all users without having to set up the spreadsheet trust location or macros for each user? I would like to have the spreadsheet accessable to any user on the PC without setting up individual access. The users can change for shift to shift.
View 2 Replies View RelatedI'm working on protecting a spreadsheet to where only certain people would be allowed to unprotect and edit it... I've used a standard password protection and created a userform prompting for password and unlocking the sheet, and this works fine.
On the other hand, I was interested if it's possible to write the code to where once the "unprotect" button is clicked it would seek User Account name from windows and try to match it with a list of users somewhere within the workbook. This way the user wouldn't be prompted for a password, but rather the unlocking would simply work or not depending on user account.
is it possible to have a userform activated as a result of a Worksheet_Change event on more than 2 cells. Currently I have the code below which will activate userforms if either a cell in Column G or a cell in Column T is changed.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("G:G", "T:T")) Is Nothing Then Exit Sub
If Intersect(Target, Range("G:G", "T:T")) = "" Then Exit Sub
If Not Intersect(Target, Range("G:G")) Is Nothing Then
GlngRow = Target.Row
Call Module3.ShowList1
End If
If Not Intersect(Target, Range("T:T")) Is Nothing Then
GlngRow = Target.Row
Call Module3.showlist4
End If
End Sub
What i need to do is activate another userform if a cell in Column AC is changed. I tried this code but got an error "Compile Error: Wrong number of arguments or invalid property assignment", which leads me to believe I can't add any more cells to the argument and it highlights the word Range in this line - If Intersect(Target, Range("G:G", "T:T", "AC:AC")) Is Nothing Then Exit Sub..........
Can you have more than 1 worksheet change event on the same worksheet, if so, how do you name it to prevent the ambiguous name error. What code would I need to select a text value in colums e11:e15 based on the cell value in cell named STATE and place the selected value in cell e16. I have, thanks to this resource, one worksheet change event that selects a numeric value from any column E3,F3:F7 and places that value in cell C4. but the same code doesn't work for the new worksheet change event.
View 4 Replies View RelatedI 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.
I want to create a log of everyone who opens a particular workbook. I'm using Excel 2003. I found this macro, and created a worksheet called "Log", but I've opened the workbook several times and nothing appears on the Log sheet.
View 5 Replies View RelatedIn another thread I was able to access another workbook with a listbox.
I'm now trying to do the same thing with a command button. Here's what I have so far without success.
Private Sub CommandButton4_Click()
With Range("'Estimate_Database.xls'!ELECTRICAL")
Worksheets(.Parent.Name).Activate
.Select
End With
Electrical.Hide
DataSheets.Hide
End Sub
The file path being C/Documents/Estimate_Database
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies View RelatedI have a script that exports an excel worksheet into an access db table and thsi is working fine. However i want to define a specific worksheet where the data is being exported from within the code but im having trouble doing, its using just a range at present.
View 2 Replies View RelatedI have a macro below that gives me a popup box that lets me type in the new sheet i want to access. etc(sheet1.xls) It give me 2 boxes one i must type the old sheet i currently access etc(sheet0.xls) and the next is the new sheet i will now be accesssing.
Is there a way to change this so i only get one box that will let me type in the new sheet i want to access?
I have an excel worksheet that I need to populate with a few thousand data points from MS Access. Currently I do this through vba code somewhat similar to this:
rst2.Open myQueryString, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
Do Until rst2.EOF
wsht1.Cells(1+counter, 1) = rst2!val
rst2.MoveNext
counter = counter +1
Loop
rst2.Close
Now this method works, but it is pretty slow to load. I remember reading as a general excel optimization technique you should avoid using long loops accessing cells on an individual basis.
Is there a better way for me to dump large clumps of Access data into Excel, instead of populating it cell by cell?
I currently have a userform, and on commandbutton_click, it performs
the following code that adds a column in a access table to a combobox(cbList)
Private Sub CmdName_Click()
Dim rstName As ADODB.Recordset
Dim strClientDatabase As String, strConnectionString As String
strClientDatabase = ActiveWorkbook.Path & "9001.mdb"......
After its listed on the combo box, upon commandbutton_click on another button, i wish to extract a particular record, based on the selection made on the combobox(cbList). This is where im having problems caused i have no idea how to do so. I just want it to extract the particular record row, based on cbList, and insert it into range A100:D100 in a particular worksheet. It is then updated and added to a listbox which ive already done the coding for, and with another button click it would add the details in the listbox into the appropriate location i wish to.. The only place im stuck is with extracting the data from access into a A100:D100 range in any worksheet. what ive come up with, but is incomplete is :
Private Sub CmdImport_Click()
Dim adoRS As ADODB.Recordset
Dim strSQL As String
Dim strClientDatabase As String, strConnectionString As String
strClientDatabase = ActiveWorkbook.Path & "9001mdb"
.........
I'm trying to access/read data from a hidden WorkSheet in Excel 2003 using:
Application.Worksheets("Hidden Sheet").Activate
With ActiveSheet
**** Data ****
End With
The accessed **** Data **** refers to another ('unhidden') WorkSheet. How can I read the data without making the WorkSheet visible to the User?
I am trying to implement code that can handle a dynamic number of objects, specifically check boxes.
I know of eval( ) in perl that allows one to dynamically create command lines. For instance, the following code would change all of the Checkbox values to 1. The syntax may not be correct but the idea is solid.
Do While i < num_checkboxes
eval("Checkbox" .i. ".Value=1)
Loop
Does this funtion also exist in excel?
I want to write down the code that will populate values in "Sheet1" from the Access table. The column headers shows "Envelope types", "Envelope Size" fields from the Access table and each cell should store sum(volume) for each month in the table.
As I can't upload access table in the attachment so I have exported data into Workbook named "tblmain" as attached. But in actual tblmain is Access table. consider it an access table.
wrting code that will fetch data from access table and store in all the cells of the table in "Sheet1" of Elevate workbook.
I have a workbook with just one worksheet. It's just a list of data imported from Access and saved as a worksheet. When I try to enter a formula ie '=4+4' it goes in as text and will not calculate. I have tried various formatting to no avail. I added a second worksheet to see what happens and this works fine. (XP home, Excel 2003).
View 12 Replies View Related