I have a workbook where it is important that users do not delete any information in a particular range. But, I do not want to 100% block users from deleting the info.
What I would like to do is, if a user selects a cell within the range, and tries to overwrite or delete, that a warning box comes up to advise the user against changing the data. Is there a way to do this?
I am looking at not allowing users to select the header and footer info. I don't want them to delete it. I have tried to protect the sheet and workbook but not working.
View 1 Replies View RelatedI am in need of a macro that will allow users to delete an entry in a named range as well as delete a range named the same. I would like the macro to open a list showing the contents of a range named "Locations". Once opened the users may select a name from the list and click on a button to remove that item from the named range. the macro can then delete the cell that name is in and shift the remaining cells up, changing the range. Second the macro should delete a named range that matches the one the user selects. once delete I would like to have a pop say "Location Removed Sucessfully".
View 9 Replies View RelatedI have this formula in my worksheet
=sum (L12*M12*N12)
How can i put this formula into VB code so that users of the spreadsheet cannot delete the formula.
I have a worksheet that I have some "locked" cells on. I also have protected the worksheet but when I check to allow users to insert rows and to delete rows and then save it, when I re-open it I can't insert rows or delete rows???
I am using some VBA to allow for the collapseing of rows while maintaining protection and that works perfectly. I want users to be able to insert and delete rows while maintaining protection. I thought a simple check in the protection was good enough, but for some reason it isn't.
I have a sheet with macros that allows users to fill in data from columns F10 to O10 from row 10 to 54.
Column P will then make some computations based on the inputs from col F to O.
Row 55 will also make some computation based on inputs from row 10 to 54.
How to I modify the code below to allow users to either add/delete columns and rows such that it does not fowl up the computations?
The computation does not have to be at column P or at row 55, they can be dynamic and change depending on how many rows and columns the users delete/add.
A little background on what the code does. From Column F to O, the code UNLOCKS the columns only if user inputs date in Cell 3 of that particular column.
Example. Column F is locked from F10 to infinity at the start. Only when user inputs date into F3 does the cells F10:F54 is unlocked for user to input data.
This goes on until column O. I want users to be able to add more columns, or delete ones they don't need.
There is a webcreated excel document that generates dates as the date of such as:
4/1/09 12:39:00 PM
I want to write a macro that will remove the date part and just leave the time. How would I write that? Times are in columns C and D and I want it to scan all the way down to row 600 and replace.
i sell solar panel systems and i've broken it down to 18 different packs that i can sell, the reason for this is that i can very quickly have a tailored quote for a particular client. what i want to do is is literally from a pull down menu have the 18 packs, pick one and for all elements of that quote to come up. i have used a data validation list for the pull down menu which works fine. for the 18 packs, each one has an array of info of between 15 - 18 rows and 4 columns wide (containg info like part description in first column, then cost in second column, quantity in third column and total cost in fourth. how i set it up was, i have all the seperate packs going for left to right on a spread sheet with the title name on top. i have created a quote area so when i use the pul down menu to get title all that pack info should come up. i have done this by using the hlookup command to return the first column on the left - description on parts and this works fine - then to return the cost in the second column, i am using a vlookup command by looking for the description on the left to return the cost. this works for 90% of the info and for the other 10% i get an #N/A when i know it shouldn't. all cells have the same format so i know this isn't the problem. i would really appreciate any help on this from anyone who has had this problem before.
View 9 Replies View RelatedI am facing an issue related to IF formula. It does show the correct result in one and another cell, but in a few others - doesn't.
This is strange since all variables are correct, and parameters are the same for every cell. And, in theory every cell should show the right answer.
In addition, the result doesn't change even when changing variables in cells that are dependants to cell where IF is located.
Concerning the formula itself. Variables are as such:
- different container types (20, 40, 40rf, 45cs, 45rf, 45)
- different weight limitations per container type (e.g. 20 container max weight 26t)
- using IF(OR(AND(....
- if any of the conditions met (e.g. 20 container < 26t ) then VLOOKUP for the rate from data based on the place name.
- if non of conditions met, then show "Overweight cargo"
When the sheet is hidden by me in the excel others shouldn't unhide the hidden sheet.
View 5 Replies View RelatedI have a combo box that insert certain information in the row corresponding to the active cell. Apparently the events that trigger the code are generated by the combo box ( change or on click) but pressing any key also triggers the events of the combo box.(this is undesirable)
The following code is excuted even when any key is pressed. Any suggestions on how I can go around this problem? (get this code executed ONLY when a combo box event is triggered)
Private Sub cmbLinkInfo_Click()
'The record is inserted only if the user is inside certain range
'The range is inside the affected links table and between the columns C and F
If (ActiveCell.Row > 25) And _
(ActiveCell.Column > 2) And _
(ActiveCell.Column < 7) Then
'<
Cells(ActiveCell.Row, 3).Value = cmbLinkInfo.List(cmbLinkInfo.ListIndex, 0)
Cells(ActiveCell.Row, 4).Value = cmbLinkInfo.List(cmbLinkInfo.ListIndex, 1)
Cells(ActiveCell.Row, 5).Value = cmbLinkInfo.List(cmbLinkInfo.ListIndex, 2)
Cells(ActiveCell.Row, 6).Value = cmbLinkInfo.List(cmbLinkInfo.ListIndex, 3)
'>
End If
End Sub
I want to delete the rows if they do not equal "TL" based on certain criteria, but save the info
then delete the rows if they do not equal "Fedex" based on certain criteria, but save the info
then delete the rows if they do not equal "LTL" based on certain criteria
After the information has been filtered it is supposed to load to a template giving monthly information for each of the above, but this is not working
VB:
If Load = "LTL" Then
Range("A2").Select
Selection.Sort key1:=Range("F1"), order1:=xlDescending, Header:=xlYes
For x = 2 To TotalRows
If Cells(x, 6).Value > 10000 And Cells(x, 1).Value = "FEDX" Then
[Code] ....
I am pretty sure that my problem lies within the parenthesis where it says Rows("2:" & x-1).Delete. Am I supposed to put something else there since I am deleting rows based on three different loads?
I have a VBA macro that deletes duplicates rows, however I would like to retain the information from the duplicate row, just before deleting it. For instance, If I have these two columns: ....
View 9 Replies View RelatedI would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies View RelatedI have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
I am after increasing the number of allowed users that can log onto a form. Currently I have used someone elses code, but that seems to only allow 1 userID to be able to log in. I am not after making the sheet into Fort Knox, just a simple UserID so I know who hasd made any changes.
Private Sub CommandButton1_Click()
If txtuserid.Value = ActiveSheet. Range("b100").Value Then
CPFRVinputfrm.Show
Me.Hide
End If
If txtuserid.Value = "admin" Then
CPFRVadminfrm.Show
Me.Hide
End If
I want to run an event for specific user. In this case, only John Doe should be able to run this command. It works fine if i put john doe in there and i get the MSGBOX since my windows log in is not John doe. But when I put my windows login there (and only I can run it), i dont get any msgbox or the rest of the command does not run..
View 10 Replies View RelatedWe have an excel file that I've developed that people in our department are supposed to be using - not that they want to - but it is an edict from our bosses. Is there a way to track who is actually using that file so I can verify that they are using it rather than just relying on them saying that they are?
View 9 Replies View RelatedAlthough I'm able to write UDFs and distribute addins, one thing I've found is that if you use a UDF formula on a sheet on one machine, save it, then open it on another, Excel doesn't automatically look in the local .xla for the formula. In fact it tries to find it on the network (expecting to locate the originating user's machine, I suspect) and then throw a strop....
View 9 Replies View RelatedI have a sheet which has many columns in it, all columns are protected except column B. When I want to enter data always I have to insert a column in column B. When I clicked insert button, my B column move to C and B column become protected and C become unprotected. My query is always I want B column should be protected (even if i clicked on insert column button too).
View 1 Replies View RelatedI have a workbook that is linked to 2 text files and updates just fine for me but when someone else opens it it does not calculate the new information just keeps the old. New text files are ran from our database to excel every night. When the workbook is opened the formulas should recalculate based on the updated info in the text files. Problem is when I open the file it performs perfectly and updates as it is supposed to but when my colleague opens the file it is not updating and does not show the new updates. I have checked to make sure the calculation is set to automatic. I have also confirmed that we are both working with 2003 version. I even had her open the text files and close and reopen the workbook and no go. I hat her do ctrl+alt+f9, even ctrl+alt+shift+f9. Had her click on the function, hit enter and nothing. Even tried find and replace = with = to wake up excel and nada.
View 9 Replies View RelatedWe use a spreadsheet for developing loans. I can open and print all of them just fine and so can almost all of the other employees. We have two people, though, that have the margins change when they open them. I can open it and have it be just fine, close it, they open the exact same file and see changed margins.
How to change that. I don't want them to have to do it on each spreadsheet. I am looking for a complete, one-time-only fix.
I am looking for a code to let users select an excel file like File Manager.
View 2 Replies View RelatedI have created some macros and assigned shortcut keys. The workbook is shared by other users. I want to keep these macros from being accessible to other users. I know that they can go to ToolsMacros and display the names of macros and run them. How can I prevent them from accessing the ToolsMacros? Or can I assign password to individual macros?
If I put the macros in individual sheets as Private, they don't show up on the ToolsMacros list, but then I can't assign shortcut keys.
can we use excel workbook by many users at same time ?.
View 3 Replies View RelatedWe have a place where all our documents are stored. We have a log where we all log in what we're working on. Our trouble is, if one person has it opened nobody else can add their items to it. Is there anyway to have multiple people be able to open and enter their work for the day, save and close it out?
View 1 Replies View RelatedHow do I share a macro(s) to other users so they can use it? Each user has different location because we are working remotely.
View 1 Replies View RelatedI have made an integrated timesheet for everyone at work. Problem is, people will be funny-beggers, and want to password protect their workbooks.
I want to be able to prevent EVERYONE from creating passwords to protect their workbook.
I've created a model that uses an add-in to calculate otherwise cumbersome formulas, and 4 or so people need to access this model at any given time. It's saved (along with the add-in) in a public folder on our network drive. Everyone is able to access the model, and is able to load the add-in directly from that folder, but the cells that use the add-in point to where the add-in is stored locally on my drive (C:Documents and SettingsmeApplication DataMicrosoftAddIns) and thus they are not able to use the add-in functions without redirecting every reference to me with references to their add-ins (basically just by finding and replacing every 'me' with 'them' in those cells at this point). So I guess what I'm wondering is how do I make it so the add-in is 'universal' (instead of local) such that as long as each user has the add-in loaded they can fire up the model and use the needed functions.
View 9 Replies View RelatedI inherited an application that a co-worker deployed as an add-in to Excel and now need to change it.
I am able to find the code in the macro to change but I do not know how to update the add-in and deploy it to other users. And naturally there it does not have any included documentation.