Insert Row While Maintaining Merged Cells - Locked Sheet

Oct 28, 2011

I have a protected sheet with merged cells. I would like the user to be able to insert a row and have the formatting (including merged cells) duplicated on the new row. I've searched and seen some different options using VB but I'm clueless as to how to use those. Is there a simple way to do this?

Copying the row and using "insert copied cells" won't work because they get an error because of the sheet protection.

View 1 Replies


ADVERTISEMENT

How To Copy Row With Formula In Locked Cells And Insert Copied Cells In Protected Sheet

Mar 29, 2014

Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?

View 1 Replies View Related

Insert Comments In Merged Cells?

Apr 20, 2012

The macro below only works for a single cell, how to include merged cells?

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
On Error Resume Next
Dim cmtText As String

[Code]....

View 2 Replies View Related

Insert Picture In Merged Cells

Dec 31, 2006

I have multiple merged cells (all same size). But not all in the same place.

I'm looking for a macro that can insert a picture in a selected merged cells and also size it to the merged cells.

I want to be able to choose a different picture from a location, for examle to network map P:.

I looked around but couldn't fins a code for this.

View 9 Replies View Related

VBA - Allow User Select Locked Cells While Sheet Is Protected

Mar 29, 2012

Here is an excerpt of my code

Code:
Else
Sheet8.Unprotect Password:="rm123abc"
Sheet8.Range("V" & rw) = "Item is not found in the Data Base. Please Contact DME for all documentation requirements for this item"
Sheet8.Protect Password:="rm123abc", AllowFiltering:=True, AllowInsertingRows:=True, AllowDeletingRows:=True
End If

Unfortunately, once the code has run and has finished re-protecting my sheet, it doesn't allow the user to select locked cells. These locked cells contain hyperlinks, and they need to click on these hyperlinks to navigate through my workbook.

So I have two questions:

1. How can I enable allow user to select locked cells
2. Do I have to allow the user to select locked cells to access my hyperlinks, or is there another option?

View 2 Replies View Related

Protected Sheet With Timestamps And Locked-after-saving Cells

Dec 31, 2013

I have this excel sheet I'm trying to create (to make my life better at work). It's to track credit card refunds. It'll be saved on sharepoint. So my department will fill in the data on the "Ops" side, and then the accounts department will fill in their side. It's very rudimentary, but anything is better than the old, tattered BOOK we are currently using.

So far, I have the user/date/time stamps autofill, when the user fills in "Entered by" or "Recieved by". I have those cells locked so no one can edit it. I have separte passwords for both sides (Ops and Accounts).

What I want now is to be able to:

1. Lock each line once it's been entered and saved. Say my department fills in one entry. We save it. After that, it CANNOT be edited. If there are any changes, I'll put in a remarks column so people can enter stuff.

2. If someone enters the first two columns (Guest name, booking number), then the "Entered by" HAS to be filled, or the workbook cannot be saved.

3. ONLY letters can be used for Guest Name and Entered by. I've tried different things for this. While I've managed to make it work if the user enters a digit first, I can still enter John56, and it'll pass. I need it to only allow letters (and spaces).

4. The "Booking Number" should only allow six digits. No less no more than six digits. It can be 000001 even. But it has to have six. And, obviously, no letters, no spaces.

In the "ThisWorkbook" module, I've got this code:

Code:
Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="hurlEy!", _
UserInterFaceOnly:=True
Next wSheet
End Sub

In the sheet1 module, I've got this code:

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 3 Then
With Target
If .Value "" Then
.Offset(, 1).Value = Environ("username")

[code]....

View 1 Replies View Related

Can't Sort A Sheet With Merged Cells

Feb 26, 2011

I have a fairly simple sheet that I'm trying to sort (and have in the past) using Data/Sort on 3 columns.

I do not know what a 'merged' cell is, so have no knowlingly formatted a cell as 'merged'. I have populated some cells with information copied from other applications, so they may be 'merged'.

When I try to sort I get the message: - this operation requires the merged cells to be identically sized. to avoid this behavior, unmerge all the merged cella in the range or....

How the heck do I even find the merged cells that are causing the problem?

View 5 Replies View Related

Auto Format Some Merged Cells Within A Sheet Based On The Information In Them

Feb 22, 2010

I am trying to auto format some merged cells within a sheet based on the information in them. how to reference the information within theses cells?

View 5 Replies View Related

Insert, Keep Merged

Nov 5, 2009

Insert, keep merged When I insert a cell using:

View 2 Replies View Related

Copying Cells From One Worksheet To Another And Maintaining Format

Aug 7, 2008

I have a worksheet that gets autofiltered by the user. I need take the unhidden data and copy it to a new worksheet.

Range("H18").Select
Dim sh As Worksheet
Dim Cell As Range
Dim Txt As String
For Each Cell In Sheets("Panel Check List").Range("H18:H5000")
If Cell.EntireRow.Offset(1, 0).Hidden = False Then
Cell.Copy
Sheets("Query Results").Range("A6").Select
If IsEmpty(ActiveCell.Offset(1, 0)) = True Then
ActiveCell.Offset(1, 0).PasteSpecial
End If
End If
Next Cell

View 9 Replies View Related

Protect Certain Locked Cells From Editing And Allow Certain Unlocked Cells To Be Changed On Multiple Worksheets?

Jan 31, 2014

1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.

2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)

3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .

View 1 Replies View Related

Web Query Fails When Sheet Locked

Jan 26, 2007

I have a web query that returns a value to the first column of the row.

I have unlocked the cell (Format -> Cells, Protection Tab, uncheck the Locked check box).

If I run my web query, it works fine. If I then lock the worksheet, the web query fails, even though the cell that I am returning the web query to is not locked.
Code below:

Sub create_pay_proposal(pUserId As Range, pRespID As Range, pWorksheet As Worksheet, pRange As Range, pParms As String)
Dim strURL As String

'On Error GoTo WebConnError 'Error handling if unable to connect to database/URL

strURL = <removed For security>

With pWorksheet.QueryTables.Add(Connection:= _
"URL;" & strURL, Destination:=pRange)
.Name = "fnd_web"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False

View 5 Replies View Related

Prevent To Take Me To Another Sheet When Clicking On A Locked Cell.

Jul 11, 2009

I have one issue with my protected worksheet. Some locked cell contents are linked to cell contents located in another sheet. When I double clicked on these cells, a warning window poped up but also opened the sheet in which the corresponding cell is located. This is quite annoying to have to go back to the former sheet. How can I prevent it ?

View 3 Replies View Related

Allow Cell Formatting On Locked & Protected Sheet

Jun 22, 2008

I have locked some cells on my Spreadsheet so other people can not change them. What I would like to know is how do I change the colour of the font? I also would like to Bold/unbold the font.

View 6 Replies View Related

Allow Locked Cell Comment Viewing On Protected Sheet

Nov 16, 2008

I just realized that some of my comments on a worksheet are being cut off or not viewable. By Default the Comment is displayed to the Right of its cell. I have a Scroll Lock to prevent users from scrolling off the work area. Some of the comments are being displayed past this scroll area and the user is unable to read the comments as they are being cut off or being displayed beyond the scroll area.

Is there anyway to reposition or set the area that these comments are displayed? For Example, to the Left of the cell so that they are able to be viewed. The Workbook is Protected and the Cells with comments are locked so the user won't be able to click on the cell. I found 2 Codes that would work if the user could either click on the cell or if the comment always visible(they are not)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim cTop As Long
Dim cWidth As Long
Dim cmt As Comment
Dim sh As Shape
Application.DisplayCommentIndicator _
= xlCommentIndicatorOnly
Set rng = ActiveWindow.VisibleRange
cTop = rng.Top + rng.Height / 2 ................

View 9 Replies View Related

VBA - Insert Formula With Sheet References In All Blank Cells In Used Range?

Sep 9, 2013

I am try to get the following VBA macro to work; however, I keep getting hung up on errors regarding the formula I am trying to input. It is getting hung up on the apostrophes and dollar signs. I am fairly new to VBA so I am lost when it come to converting my sheet formulas to VBA.

Code:

With ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks)
.Formula = "=RAND()*0+VLOOKUP(INDIRECT(ADDRESS(1,COLUMN(),3),TRUE),INDIRECT("'"&TEXT(INDIRECT("$A"&ROW(),TRUE),"DD-MMM-YYYY")&" Inv'!"&"$J:$K",TRUE),2,FALSE)"
.Value = .Value
End With

View 3 Replies View Related

Copying Merged Cells (3 Cells) Based On Contents Of Any Of 3 Cells To Right

May 29, 2014

I wish to copy a merged cell (3 cells) based on if only 1 of 3 cells to the right contain "X". if the top cell does not contain "X" than the merged cell is not copied. Also, is therea more elegant to copy 3 columns at a time rather than do one at a time as my code shows:

Sub CopyICUCAPU()
'
' CopyICUCAPU Macro
'
Dim i As Integer

[Code].....

View 14 Replies View Related

VBA To Clear Contents Of Cells And Keep Some Cells Locked

Jan 25, 2010

I have written this code to clear the contents of certain cells, lock the content of others and protect the sheet again it works on sheet1 but not on sheet 7. This is suppose to happen when the Print button on my sheet is clicked.

View 4 Replies View Related

Jumping Locked Cells

Oct 21, 2008

kindly help in jumping locked cell in a proteced worksheet..

View 9 Replies View Related

Check Locked Cells

Nov 6, 2008

Sub PastSpec()
If Selection.Locked = True Then Exit Sub
On Error GoTo ErrHan
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ErrHan:
Exit Sub
End Sub
I recorded part of it and adapted it afterwards. You will notice I have a line to check for locked cells, this does its job if the cell selected is locked. However if the cell selected is unlocked and the cell below is locked the code still excutes and pastes.

View 9 Replies View Related

Can't Click Hyperlink In Locked Cells

Jun 10, 2013

Any way to make it so that a cell can remain locked but be able to click a hyperlink? I need the cell to remained locked so nobody can alter the hyperlink name and value but I would like users to be able to click it and have it open in their browser.

View 9 Replies View Related

Locked Cells Have Changed Formatting

Jan 23, 2014

On an excel document that was originally formatted as a number and then locked, somehow users have changed the formatting to a date. I think it is an accident, but I can't figure out how they are doing it. I have several data files on a server where several offices have access to them to input data and somehow formatting gets changed sometimes, even when locked.

View 3 Replies View Related

Macro Is Unlocking Locked Cells

May 7, 2008

I'm using three different workbooks for the macro I've designed. The macro works exactly as I planned it would, but I'm getting a curious side-effect of running the macro. I have over 5,000 workbooks that I need to open, unprotect, change (mostly by copy/paste), and reprotect. It's a fairly straight forward macro, but it is my first, and I must be overlooking something. When the macro is complete, cells that were not modified via the macro in any planned/anticipated way are being unlocked. This has left me puzzled and frustrated. The files are opened, unprotected, the cells are copied over as planned, reprotected, and closed/saved. Everything works perfectly, but the cells in parts of the workbook (Filename) (which were locked before the macro ran) have been unlocked and are no longer protected when the workbook is reprotected. Again, these cells were not within the ranges modified, and I'd rather not have to format every cell in every one of the 5 sheets of (Filename) to be locked.

File names are:
"Finished Goods Inventory TREE (ToDMSI).xls" (alias: Workfile - sheet providing file path to be updated)
"Random_workbook_selected_from_previous_filename.xls" (alias: FileName - the copy-to file being updated)
"MACRO TEST BOM Master.xls" (the copy-from file always open)

Comma Delimited table layout in "Finished Goods Inventory TREE (ToDMSI).xls":
Col A, Col B, Col C, Col D
Customer ID,Item Code,File Name, Directory
4FRE01,4FRE01-0001,4FRE01-0001.xls,4FRONT

Here's the

Public FileName

Public Workfile

Public ItemCode

Public CustCode

Sub OpenBOMSeq()

View 14 Replies View Related

Locked Cells With VBA And Ability To Filter

Oct 23, 2008

how to create two buttons so that I can have a data entry specific protection and then a lock all button. Here's the link to the thread:

http://www.excelforum.com/excel-prog...-a-button.html

And here's the code I'm currently using:

View 3 Replies View Related

Select Locked Cells In 2000

Jun 15, 2009

I've created quite a few workbook/worksheets in excel 2007 compatibility mode that are data entry intensive. When protecting these worksheets, you have the option of allowing the user to "select locked cells". I invariably uncheck this option because the user doesn't need to interact with these cells.

However, several of my users are running Excel 2000 and the same option is not available when one protects a worksheet in Excel 2000. Is there a way to do that in VBA for these Excel 2000 users such that when they click on a locked cell, the cursor will not respond to that cell?

View 5 Replies View Related

Edit Locked Formula Cells

Aug 12, 2006

I have a template that I have hidden the formulas but allowed editing. Is it possible to create a pop up box for the end user to use to edit a cell with the hidden formula? This is a budget template with already set up formulas but the end user needs to be able to make edits but the end users are beginner excel users and if they double click on the cell the formula will automatically delete leaving the cell blank. I need a way around this so they cant view the formula but only change the data if they really mean to change it.

View 3 Replies View Related

Identify Locked Cells On Worksheet

Oct 19, 2006

Is there an easy way to show/return any Unprotected Cells on a Protected Worksheet?

View 8 Replies View Related

Color Protected/Locked Cells

Oct 7, 2007

How can i set up my spreadsheet so that all protected cells are a different colout to the main sheeT.

I would like this colouring to appear in the sheet when in protected and unprotected mode

View 4 Replies View Related

Filtering Column Cells And Maintaining Other Column Values

Mar 11, 2014

I have a spreadsheet with multiple columns:

Column B - Host Contact
Column C - Company Name
Column D - Attendees
Column E - Contractor Position

Column B will have anywhere from 1 to 10 names.
Column C will have 1 -2 names.
Column D will have anywhere from 1 to 10 names.
Column E will correlate with column D.

This list will go to multiple people who will want to filter column B for their name. When they filter their name, they should see all values in columns C, D and E.

When I try to filter this spreadsheet by a persons name in Column B, it only shows me the first value in columns C, D and E.

One of the attached pictures shows a mock-up of the unsorted list, the other shows the sorted list.

Sorted Format.PNGUnsorted Format.PNG

View 1 Replies View Related

Clear Contents Of All Unlocked Cells (many Are Merged Cells

May 28, 2009

I am looking for a code that will clear all of my unlocked cell in sheet 1. That is not a problem but since many of the cells are merged I know it keeps throwing me an error saying cannot change contents of merged cells or something like that. Does anyone know how to get around this without unmerging the cells. I saw a code to unmerge all of the cells on a sheet but I really don't want to do this as I already have worked around most of my problems with the merged cells.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved