Is there a way to setup a spreadsheet so data that has been entered cannot ne deleted or edited, and only the last field in the column can be accessible? So if I have info entered in a1, a2, a3, b1, b2, b3, no one can edit or delete them, and can only add to a4 and b4? Will I need to write a macro for this or is it something I can do with certain settings?
View 9 RepliesI have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).
Option Explicit
Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function
Two requests:
1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?
2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
Is it possible to edit my .bat file using vba?
View 1 Replies View RelatedI am using Excel 2000
I have recently opened a file sent to me, and since closing it my Edit menu does not work properly when I open any existing files or start a new file
The problem is with Copy, Cut Paste and Paste Special, when I click on the Edit menu they are all greyed out, also if I right click they are also greyed out
Obviously the file I opened has changed some settings, how do I re-set this
The edit box used to make or edit ranges for Defined Names is VERY difficult to use for long char strings. The arrow keys do not move the cursor within the edit box & the end key does not work.
How to edit range names that are 100-200 characters long?
Are there settings to alter behavior of arrow keys withing these "range definition boxes"?
I have an autoshape with a text box in it. It is one of those "scroll" shapes. I want to be able to edit the text in the box with VBA, but I don't know how to call it because I can't seem to find the properties for it. I tried recording a macro to do it, and it called it:
View 5 Replies View Relatedhow to set up and/or edit my macros so that I can use them in various outside workbooks without having to open the specific workbook I put the actual macros in?
View 2 Replies View RelatedWhen I list my macros using alt+F8 the edit option is no longer available, any ideas what I have done?
View 9 Replies View Relatedhow can I open a .txt file with vba and replace alll "," with ";" ?
I am facing problem with my footer, which is as under:
___________________________________________
E-mail: xxx_yyyy@somewhere.com
My footer should be printed at the bottom of my paper as shown above. But, as expected, when I change margins of page or even change the printing preference, like best print to draft print or like that, it goes changing like this:
________________________________________________E
-mail:xxx_yyyy@somewhere.com
or like this:
_________________________________________________
__E-mail:xxx_yyyy@somewhere.com
So, it become headache for me. i have to first see the print preview and then after I can go for printing. How to deal with this problem?
There should be two lines in footer: first will have _____________________________________only and the second one will have E-mail:xxx_yyyy@somewhere.com
My purpose is to give my endusers a workbook with a macro in it which applies changes to existing workbooks on their computers. I want the enduser to select each workbook to apply the changes and have the macro capture the names of the workbooks so they can verify that all of the appropriate workbooks have been changed.
I’m trying to change two types of data: (1) text, (2) formulas.
Here's what little I have, but not much is working.
Sub ChangeBACKLOG()
MsgBox "Open the FILE to which the Backlog fix is to be applied."
Dim targetWorkbook
dlgSource = Application.Dialogs(xlDialogOpen).Show
Set sourceWorkbook = ThisWorkbook
Set targetWorkbook = Workbooks.Item(Workbooks.Count)
On Error Resume Next
I've been trying to resolve an issue with the userform that i created. It adds new records to the sheet "Data" but i can't seem to add a search/edit function to it.
It could be either a combobox with the existing Project Id's or a text box + a control button, so a user could enter Project Id and hit a button.
The spreadsheet example is attached : Project Entry Form.xls
Is it possible to edit multiple =VLOOKUP formulas to add in a "[range lookup]" = FALSE without editing each one individually? I was going to use a find and replace for the "col_index_num" and add the FALSE to the end of that, but in this case my "col_index_num"s vary too much.
View 6 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 suddenly can not edit in a cell by double-clicking on it. I can only edit in the fx bar on top. Other sheets in the workbook still work normally. What causes this, how can I fix it and how can I make sure it doesn't
View 5 Replies View RelatedI need to edit the hyperlinks in my excel sheet. I know how to do this manually and edit one at a time, but I need to change 3000 of them!
Is there a way I can do this without going through each one?
i have an excel sheet that has a cell with a formula in it. This cell has been locked by the previous user.
I am unable to click on it and it because it is showing a #NAME? error, i need to edit it somehow. Is there some way i can edit it?
The formula below works apart from the first bit which i want to be if F23 says 'fail' then i want the cell to say 'no dissertation' but if f23 says pass then i want the formula past that bit to be put into action.
=IF(F23="Fail",0)*IF(COUNTIF(B22:B45,"Dissertation")=1,IF(VLOOKUP("Dissertation",B22:C45,2,0)>39,LOO KUP(F26,{0,40,50,60,70},{"No Dissertation","3rd","2:2","2:1","1st"}),"No Dissertation"),"No Dissertation")
isolation123
Heres a strange thing, My latest version of a spreadsheet i have design has got a small problem. When clicking on a cell, nothing appears in the edit window, and when you double click on the cell to edit the contents the data disappears.
This only happens on one sheet within a workbook of 7 pages. The werid thing is that it does not happen on the version before
Any ideas how this could of happened and is there a fix?
I've read a bit on the web about there being a bug with the 'edit links' feature. How/Can I get this function to work?
View 6 Replies View RelatedI have a worksheet with what appears to be symbols or text boxes; squares, some with a check mark in them, some without. cannot select any of them to edit or delete them, I can type in the cell behind the symbol, the symbol hides the text. I can copy and paste them, but not delete them. how to identify them to edit and/or delete them. Password protection and all cells are unlocked as well. I have stumped as well as other forums.
View 4 Replies View RelatedI have a macro enabled spreadsheet in which I am able to paste in the customer number as text, but I need to touch the cell in order to make the customer name populate in the next column.
Double click - enter and F2 -enter both will do this, but I'd rather not be doing this for 200 rows every time I have to use it. Is there a way to touch all of these rows without VBA? The spreadsheet was built by our IT department years ago and is rather fickle. I can't risk interrupting the code or I end up having to close it and start over. It's password protected.
Text to columns doesn't work.
i have a worksheet that gets data from web - its automated, but the website goes through a tunnel - with security - requires username and password
the Query Runs ok and smooth but i have to login manually by right clicking on a table where the query is and selecting "Edit Query" so i can login, excel vba doesnt save passwords for that part...
one way i find it possible to automate that part too would be to use maybe like the sendkeys statement to pop up the context menu from right clicking the mouse button, or like the options button on the keyboard.
I found this one:
[Code] .....
It works but the context menu comes exactly from where the current mouse position is..
I needed it to do the right click on a specific range in the worksheet (where the web query is)..
I have created a complaint database with a spreadsheet and a userform in Excel. Only part of the information is known at the time the user enters the complaint. I have created a search feature so I can search for a record using a unique identifier called RecordNo. I can call up a record and change the information in the record or add additional information. When I hit the command button save it stores a duplicate record with the new information. I don't want duplicate records.
Also some of the data fields do not fill the original data. It seems to be inconsistent in the way it loads the original record data into the userform. This issue happens consistently with the weather items that are check boxes and also with the Complaint Taken By field. I am a beginner and have found most of the code to write this database online. So perhaps there are some conflicts between code from different sources. I've uploaded the spreadsheet and userform.
Forestry Complaints WIP.xlsm‎
I get a report that I have to manually edit everyday. I've done some VBA, but not sure where to start with this one.
The attachment has two sheets, first is what I get and the second is what I want. I'll explain the process i have to go thru now and maybe I can get some help to automate it.
First
-Add the tech number to the first column for each job for each tech.
7988 us the first tech number.
-Then delete every row that isn't a job.
-Add a column with the install date.(report data near the top.)
-Add a column and populate it with the system, which is the sheet name.
Thats why its example.
I have a worksheet, attached, that is a supplier response to a purchase order. Column E contains the original qty ordered of each part number, Column H contains the original price. Now, I've created a quick macro, (in the workbook), that copies the original values to the confirmed values, and the formulas in columns G and J calculate the qty backordered, or flags the part as a price change.
After copying the original PO values to the confirmed values, the supplier would need to go in and make whatever changes are necessary. For example, we ordered 3 and they shipped 0 of a certain part, or the price has changed.
What I would like to do is create a macro that pops up an input box where the supplier enters the Keystone part number, (column A), then enters the new quantity or new price, and those items are automatically changed on the response form. I picture it in the form of 3 input boxes, in the first box the buyer enters a part number. A second box pops up asking if it's a qty change or price change. They would enter the value in a 3rd box, and that value would automatically be changed on the response.
I have a userform that I have created to enter new part information to a spreadsheet but I am wanting to also use the userform to edit exsisting part information. My spreadsheet consist of part #, name of part, description of part, date manufactured, and date sold (Column A-E). My question is can a userform be used to edit information that is already present on the spreadsheet? I am looking to edit any entries that do not have a date in the date sold field.
View 3 Replies View RelatedI've got this small (?) problem here. In a range of about 150 rows and 30 colums, I have to edit the cell formula, i.e. add something to the existing formula. For instance, in all the cells in column C I need to add " + D9" to the existing formula, in all the cells of column D I have to add " + E9". Is there a way to do this in VBA ? I would already happy to do it column by column, but I'm really reluctant to edit 4500 cells manually.
View 2 Replies View RelatedRight now i have two worksheets:
"900 - Reel"
"OverRides"
On the "900 - Reel" sheet i have a userform ("userform4") open when double clicking a specific cell. Upon opening it uses the following code to go to sheet "OverRides" to find two specific values and imputs them into "textbox1" and "textbox2" on "userform4"
I've been searching on the internet for a solution to this (if it exists). I have a Userform and I want to include a listbox(or list view or anything like it) which allows the user to select a specific row by clicking a checkbox, and then the next columns will be activated to select numbers in several comboboxes. My goal is simply to add checkboxes and comboboxes to the list view.
I just want to know if this is possible in any way and how would I proceed in order to achieve it.