Restricting User To Only Update A Certain Range In A Spreadsheet
Mar 6, 2007
I was wondering if there was a way that I can have a user only able to edit the worksheet that pertained to them in a workbook of many sheets. So if there was a sheet called "Alicia" and another one called "Love Child"; user Alicia would only be able to update info on the "Alicia" tab and user Love Child would only be able to update their info. Is this possible? Also can this be done for a particular row in a spreadsheet? So if row 3 has Alicia's info, she's only allowed to update the info on that row.
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem ... .Fields.Item(1).value = activecell 'activecell value = "Joseph" If Not isempty(activecell.offset(0,1)) Then .Fields.Item(2).value = activecell.offset(0,1).value Else .Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works End If ... End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty ' or .Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
If I setup a spreadsheet for others to use and I want them to enter their own numbers in certain cells... BUT... restrict those numbers to be from, say 0 to 50... what is the formula or how is it done? In this example I do not want to allow negative numbers or numbers > 50.
I have a primary excel spreadsheet that I work from. The architecture is as follows:
Sheet 1: Called "Information" Column A: Name Column B: Date of Birth Column C through Z: Various bits of information.
Sheet 2: Called "Master" Cell B1: Contains the date and time of last update from the VBA I am asking for below.
On a weekly basis I get sent a "Update" spreadsheet that is constructed the same way as my primary. This is what I would like to do with some VBA:
From my primary sheet I run the VBA and it opens a pop up that allows me to select the updated worksheet. Next it cycles through both worksheets (Primary and Update). It compares Column A and B, if it finds a match it updates columns C through Z from the "Update". In order to get a match cell A1 and B1 of the primary worksheet has to match Cell A1 and B1 of the update sheet exactly.
The second thing I would like it to do is if the update sheet contains a new entry...in other words the update sheet has a row that does not match the primary it copies the row from the Update sheet to the Primary. In this way, the Primary sheet is always growing with new information and updating any old information it matches.
The last thing I would like to do is copy the current date and time after the comparison is complete to the Primary workbook to sheet "Master" in cell B1. This way I always know the time and date of the latest update.
I would rather not have to update each date manually so could any of you kindly inform me of a command whereby all of the 2008 dates can be updated to 2009?
In the attached example I can't add more than one name at a time without closing the user form. I can edit the list and click the "Save Changes" button and the changes happen. But using the "Add Name" button I can not add more than one name without closing the form, it just overwrites the previous name unless I close the form.
I have a cell that displays the user name and would like the cell to update whenever the file is opened and display the user. Below shows what the VBA module code. The cell has this formula "=Usrname()".
Function Usrname() As String Usrname = Environ("username") End Function
I want to automatically update data from one spreadsheet to ther one i am novice to vb programming
here are two file plzzz help me out --sheet 1--sheet(data of sheet one to be copied into) and is there any way that the data copied also gets saved the in that spreadsheet as next time data in sheet 1 is updated!
I have a spreadsheet that update from the data warehouse (I do this manually daily) there is always new sports being added
When I update I would like a macro to check for new sports and if there is one then msgbox me and add it to my table on the next sheet that I use for lookups.
I need to have all the information in one workbook for all 100 employees and still provide an individual report for each user and an overall report. In the attachment is a "Summary" and "Calendar" sheet these are the original individual files. summary contains personal information and the "calendar" tracks absence and holiday and the totals returned to the Summary sheet. I think what I need is a Master worksheet that has all the information in for each employee pre row and to have a "calendar" input/change/ call worksheet sheet to call an individual detail, this would also update the master worksheet with any changes.
I have about 5 text boxes. The user enters data in each text box and, as of right now, the data automatically gets saved as I am using the _change event. (TextBox1_Change()) This stores the value of each text box into a cell of my choosing on an Excel spreadsheet automatically.
From what I gather (according to a great ozgrid administrator ), it would be better, as a coder, to not use the _Exit event and instead, perhaps I should use the _Enter event. (TextBox1_Enter.) This way, when the user fills out the information in the text boxes, the information will NOT be loaded onto an Excel spreadsheet automatically as they type. (Which is what it does now by using the following code for each text box
Private Sub TextBox1_Change() Sheets("Sheet1").Range("A" & intRow.) = TextBox1.Value........
I have designed a spreadsheet in the office where I work, and think it would be quite flashy (gimiky) if I could have the spreadsheet show the name of the user logged on.
At the minute if I try and access a sheet currently open by another user it will come up with the standard message 'workbook1.xls is currently in use by xxxxx' then you open a read only copy until they have finished.
I was thinking there must be some way of importing this information to excel to display something like 'welcome (xxxxxxxx)(user currently logged on)'
The other part of my problem is that the excel sheet will only display the network uername of the user, but I have seen spreadsheets that cross reference this to the Microsoft Outlook global address list on the MS Exchange server, and display the users actual name.
In a nutshell, I just want to know is there anyway the excel spreadsheet can display the username currently logged into the shared spreadsheet.
I have a list of Users in Column A on Sheet 2 and a list of Extension Numbers in Column B.
I'm looking for a way to populate a Cell (F5) with a User and Cell (H5) with the Extension number. I then enter Data into Cell (G3), (G9) and (G10). I would then on enter on Cell G10 update the relevant columns in Sheet 2 and move on to the next user.
I'm trying to code so that any changes made to Excel document will automatically change in PowerPoint. I have Copied and Pasted Special link so that its connected but when ever a change is made in Excel in powerpoint I get a pop up box saying I need to press update. But I want to bypass the pop up update box and have it so that powerpoint automatically updates when I change something in Excel.
This is the code I have so far. I don't know if it's right or if I am on the right track as I get a compile error saying I need a End with.
I am trying to make a userform that will update a list in the spreadsheet. Basically if a user types into a multiline textbox numbers (separated by line) like so:
5 10 15 20
then the macro will paste that data at the end of a list so that each number is in a new cell. The only thing I have that comes close is Range("A1").value = listbox1.value The problem with this is that it will input the entire list into one cell. I have attemped various things, like trying to get the list into the clipboard and pasting, but I haven't really had much luck.
I am working on a manpower spreadsheet and need for one spreadsheet to extract data from another and automatically update if it is changed and I am struggling below is an example of what is on spreadsheet 1:
Mentor.jpg
and the information from spreadsheet 1 is copied to spreadsheet 2, however if amendments are made to spreadsheet 1 they are automatically updated on spreadsheet 2:
I am trying to do is create and link and user form to put information into a spreadsheet. Layout of the form is done but I'm not sure how to link the cells. Also, there are some cells pre-filled that should show up on the form but I don't want the user to change those (columns a,b,c - guessing i will protect those).
Would be great if the user could navigate rows within the form so I included buttons for previous and next entries (this feature would be very similar to the built-in data form that excel provides). My sample spreadsheet is attached.
I’m looking to use a user form to populate a quotation template, I have been able to link the first part of the form to the sheet as these are static cells but I’m struggling in getting the items details area of the form to work as I need. Below is a brief description what I need.
Item Details area
Move down a line after every time the add button is pressed and clear contents of the text boxes for the next item.
Also I want to make each * denoted item required before you can add a new item
I’ve uploaded a copy of the file if my description isn’t too clear
attached is a sample schedule, there is conditional formatting in all of the cells (see graphic) such that if the cells to the left are the same value, then the cell is turned white, and a border is removed.
This gives the appearance of a 'block of time' while which cleans up the appearance of the spreadsheet.
the problem i have is that when we scroll to the RIGHT, we lose track of the 'left most' cell...which is black of course, the similar ones to the right are intentionally whitened out.
a perceived solution would be to change the conditional formatting of the column (actually only the cells in the column we care about) when it is NEXT to the vertical freeze pane so that it appears not in white, but in black font, etc.
the TRICKY part is that we will obviously need to go back and forth, so the columns will need to change formatting often.
or
when the column number (or letter) next to the freeze pane changes, change the formatting to certain cells in that column to an alternate version; and change the formatting of all other certain cells within other columns to original formatting[
I have 4 textboxes that the user enters data. I then have a button on the user form.
i would like to write code so that when the user hits the button... Textbox1 data goes into active cell Textbox2 data into the cell below Active cell Textbox3 below it and textbox4 below it then close the user form.
I need to import some spreadsheets that are sent to me into a master database spreadsheet. I have attached both below. The complaintstest.xls is the master database and the other is the sheet that will be imported. What I would like to happen is when the macro is run, the user will be able to select the sheet they would like to import through a dialog box. After the sheet is selected the macro will import the cells from the selected sheet into the master database in a particular order. In the master database example on row 2 I have listed the columns from the sheet to be imported in the corresponding column on the database sheet. This will macro will be run a couple of times a week so the macro will have to find the next empty row to start the import on.
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.
Is there a way to protect some cells of a spreadsheet from user input (by typing only), yet letting button controls and VBA macros modify the values of those cells?
I need to prevent users from accidentally deleting formulas or values generated by VBA macros or controls, although they may delete values in some cells.
I have created a userform but just unsure how to format the code in order to get information to appear in a spreadsheet http://www.srfl.ca/userform.htm
