I am instantiating Excel 2003 from vb.net and populating the spreadsheet from SQL. The workbook itself will be maintained even when the .Net program is inactive (so that the column headers are established and saved.) Is it possible to prevent the users from inserting columns within a certain range?
View 2 RepliesI have a sheet with 2 tabs. On the first tab is my data and the 2nd tab is the formula.
This is my formula;
=IF(ISERROR(VLOOKUP(A3,Data!$A:$AD,23,0)),"",(VLOOKUP(A3,Data!$A:$AD,23,0)))
When I insert a new column at 'A' the formula changes (as below)
=IF(ISERROR(VLOOKUP(A3,Data!$B:$AE,23,0)),"",(VLOOKUP(A3,Data!$B:$AE,23,0)))
How do i stop the reference changing from 'Data!$A:$AD' to 'Data!$B:$AE' when inserting the column?
This site provides a partial answer to my problem- http://www.j-walk.com/ss/excel/tips/tip98.htm
That link worked for me but there's a problem with it, it does not work if a table has different types of validations.
I am currently setting up protection on an excel sheet - I have a proceedure to open a specific worsheet and lock and unlock cells according to whether or not certain information exisit. My question is, although I can lock cells and prevent users changing them using code I can't seem to stop them selecting cells in the first place - this is obviously achieveable from the 'Protection'>'protect workbook' menu but I want to do it from code since the sheet is unprotected and reprotected a number of times during a session....
View 4 Replies View RelatedI know how I can protect worksheet or a group of cells, but I can not prevent any one to damage it by (open with) WORDPAD to clean or damage it then to cancel this protection!
View 4 Replies View RelatedI have macros that pulls data from an SQL db. Users need the ability to modify the data on the worksheets BUT they can NOT have the ability to save the document.
Is there a way to disable the "save" function from the FILE drop-down box?
I am creating a userform and would like to be able to automatically enter the name of the person using it. The way our network is set up is Username = 1st 5 letters of surname + 1st 2 letters of forename. Due to this i cannot use
Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function
as this returns the login name as opposed to the full name. I also cannot use
Function UserNameOffice() As String
UserNameOffice = Application.UserName
End Function
due to the fact that the office settings are defaulted to "Any Authorised User" unless manually updated by the user.
The users full name is displayed at the top of the start menu, but I have so far not been able to find a way to pull that into my form.
I have a macro recorded that inserts into column E in the attached report, and then retrieves data from another worksheet in the workbook. In column D I am trying to keep a 6 month average which includes the latest inserted data, however every time I insert a column the relationship with column D is moved out by one. e.g. range E8:J8 becomes F8:K8.
I am also stuck with setting up a macro to import a new model into the report. I have set up a worksheet called new model with the manufacturer, model and fail descriptions, however all macros I have recorded fail when trying to insert the manufacturer and model into mulitple cells.
I have a spreadsheet that is very large and formated a certain way. I want to make sure that a user cant put the cursor in the lower left corner and copy cells by dragging, copy & paste, or do anything other than input data and be able to highleght text and reinput data in case they mistyped or misspelled the data.
ex: user clicks on cell E33 and types in '1234568' and then move to next cell. The user can click back on E33 and can correct by retyping. That is all the user is allowed.
The cells in question are also in ranges if this helps. EX: 'E33:AJ67'
I have a piece of code that hides unneeded rows, it does work but it is very slow.
This is in the worksheet part of the project. My problem is as it runs it "jumps" in to a function I have in a module that counts continuous rows. I would like to know why it is doing this and what I need to do to stop it? I have another piece of code that is structered exactly the same that hides unneeded columns and I do not have any problems with that. I know both pieces of code are dealing with rows and I think this is part of the problem but I can not see any reason why the first bit would call the second bit?
Dim C
With Worksheets("CEN OAS"). Range("D5:D378")
.EntireRow.Hidden = False
End With
For Each C In Worksheets("CEN OAS").Range("D5:D378")
If C.Value = "" Then
C.EntireRow.Hidden = True
End If
Next C
Function to count used rows....................
I'm just looking to prevent users from using the print screen or the alt print screen buttons on the keyboard. I have this script that works if I use "39" (Right Arrow)but wont work if I use "42" (Print Screen Button).
View 5 Replies View RelatedI am putting together a corporate document which requires many different peoples access. It also requires these people to update/comment within specific columns to them.
I am really struggling with editing the columns to ensure only these specific people can access them.
Essentially I want to the document to be accessed by everyone, but column A can only be edited by Person A, column B can only be edited by Person B etc.
I've used 'Allow Users to Edit Ranges' and added a new range with a specified person - but this has not cured the problem, other people can still edit the column I'm trying to prevent them to.
Is it a case of just 'allowing' the specific person, or do I also need to 'deny' absolutely every other person to ensure that it works?
how do I restrict editing to certain columns/Rows and allow some to be edited by users?
View 2 Replies View RelatedI'd like for my spreadsheet to open with only Column A visible; I'd like all other collumns to stay hidden.
I want to create a marco to unhide column(s) based on the value of an individual cell.
For example:
if I enter the number 5 into cell A1, I would like Columns B:F to unhide.
if I enter the number 2 into cell A1, I would like Columns B:C to unhide. Is this possible?
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.
I am trying to create an invoice but the invoice show #N/A in certian columns when there is no number which already have formulas, how do I modify them using the if function?
View 9 Replies View RelatedWhat is the easiest way to stop users inserting new columns or rows into a workbook? Is it a case of protecting each sheet individually?
View 6 Replies View RelatedWhen you put a label in a column, that is wider than the column, and you don't wrap it or shrink to fit, etc. it "spills" into adjacent columns so the whole label is displayed. In some cases this might be desirable, but in others not.
You can prevent it from spilling by putting a blank space into the next adjacent column -- that truncates the display of the label -- but clearly this is not a good solution.
Is there a way to prevent labels from doing this so that only the portion of the label that is in the cell where the label is entered is visible on the spreadsheet? Of course you can see the entire label by selecting the cell itself, and that's what I want to do without it spilling into other columns.
I have a sheet that uses Blocks of cells eg: A1:X10,A12:X22 etc to hold data/text etc.
I am filling colums A to V with data and Ranking and summing them in W & X,
I don't need to show K, L M ...for example, until they are totally filled, so I hide them manually until needed.
Have a Macro to print "results" A1:X10,A12:X22 etc, and I set a "print area" or "print selected"...works fine to fit to 1x1 Page.
Problem: If I hide L, M, N etc, the macro runs fine but still uses the unhidden size of the selection to fit the page ie: I get A-B-C-D...-W-X as required, but the other half of the page is blank, because the size selected is for all including the hidden columns.
Q: How can I (Simply) print what's visable to 1x1 Page, fitting more in each time I unhide a column or two.?
i have a spreadsheet that data is imported to from a different program. however the amount of rows of data will change from day to day. I need to be able to select this range of data before i run a macro on it.
View 11 Replies View RelatedI'm using the following code to delete columns:
Dim rng As Range
Dim i As Integer, counter As Integer
Set rng = Range("1:1")
i = 1
For counter = 1 To rng.Columns.Count
'If cell i in the range contains an "x",
'delete the column
If rng.Cells(i) = "x" Then
rng.Cells(i).EntireColumn.Delete
Else
i = i + 1
End If
Next
My problem is that I have cells in other worksheets linked to the worksheet that is running this macro and everytime I run it, I get a handful of "#REF" errors. I think this problem might be solved if I could simply delete the contents of the column rather than deleting the entire column. How can I modify my code to do this?
One of the fields I want them to enter is a library branch and I have used a data vaslidation list to provide a dropdown box.
To make it act more like a windows drop down box I have hidden 7 cells above this box with the 7 possible entries. This means that with autocomplete, if they start typing in the cell it will autocomplete the entry for them.
This work fine, but if someone presses enter from the cell above my hidden rows, one of the hiddenn cells is selected!
WHY!
I have no explanation for this behaviour, nothing like it has ever happened before, and I have no idea how to get rid of it.
example attached
I have data in 4 columns, A, B, C, D. Each column has 5 rows of data. I want to write a macro to automatically insert 2 columns, after each column in original dataset.
View 8 Replies View Relatedwhat i want to do is have an area where i can input data and then i have three options of inputing the data or resetting data that is already in the list of data so i start to create a list of data but i can reset/change values if i wish for example quantity. i would also like to have a way to subtract or add a number from the quantity assigned to a code/name of some form. i know that's quite a bit
View 3 Replies View RelatedI have recorded a basic macro that allows me to copy a formulas in cells CF11 to CH11, and it pastes it into cells in columns CJ to CL. Copy of code is below if you need it.
The issue I have is that I need to insert new coumns into the worksheet, and I need to copy the formulas from columns CF to CH, and they will now need to pasted to columns CN to CP. Note that this is a monthly report where we keep the prior months, so each month will need to add new columns. There are 8 tabs in the workbook, and they all use the same macro, just over different rows (columns all line up).
Is there a way that I can get the macro to paste into the correct column without me having to adjust the macro each time?
Copy of code is as follows:
Range("CF11:CH11").Select
Selection.Copy
Range("CJ11").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("CJ20:CL20").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
[code].....
When I am saving my spreadsheet as a text file, I have 1 column with formulas that I would like to not show up in my text file. I have tried hiding the column, and have Googled for awhile now.
View 2 Replies View RelatedI have been struggling to figure this out for several days now and i don't really know where to go from here.
I have 2 columns and i need to break them up at certain points.
The attached excel file should hopefully be clear enough to show what i mean.
The first tab is how the data starts the second tab is how i want it to look.
To clarify i need to break it after each change in the first column. ie from 1 to 2.
Furthermore, i need to break the "sub groups" the second column up after 15, and 30. I will never have a number larger than 32 in the second column.
I think the excel file will clear up what i mean.
I have a very simple inventory spreadsheet that I used to keep track of certain products. The issue is that I often have to insert new columns and when I do, the formulas get convoluted. Is there a trick to making them follow the structure of the formula before?
My formula is =SUM(GT4:GU4) and appears in the Running Total column. When I insert two columns - a white column where an order will be inserted, as well as a running total column. When I perform the insert, the formula for the inserted columns, as well as the set of columns immediately to the right require corrections:
Inserted columns: =SUM(GT4:GW4) [Should be GV4:GW4]
Columns to the right: =SUM(GV4:GY4) [Should be GX4:GY4]
I know how to manipulate the formulas correctly, I would like to keep a clean file for my predecessor.
I have a VBA macro I recorded that won't insert and copy columns into the proper areas. When I run this, It inserts every column all grouped together.
For example: I want to insert a column before column D, and give certain cells formulas. Instead, it inserts it before column C. The whole macro seems to, when ran, insert EVERYTHING before the range of data I am trying to split up.
Here is the
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/30/2008 by kmayfield
'
'
Range("C3:F3").Select
ActiveCell.FormulaR1C1 = "Jan-2008"
Range("G3:J3").Select
ActiveCell.FormulaR1C1 = "Feb-2008"
Range("K3:N3").Select
ActiveCell.FormulaR1C1 = "Mar-2008"
Range("O3:R3").Select
ActiveCell.FormulaR1C1 = "Apr-2008"
Range("S3:V3").Select
ActiveCell.FormulaR1C1 = "May-2008"
Range("W3:Z3").Select
I have a feeling the answer to this will be 'no' based on the searching I've done, but is it possible to allow formatting of rows/columns on a protected worksheet, yet prevent the user from unhiding some rows/columns I don't want them to see? I've allowed them to format rows/columns so they can "size" them to fit the text they enter, but that has opened up the ability for them to unhide columns. Using Shrink To Fit would be cumbersome due to the number of cells involved.
View 5 Replies View Related