 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2006Hiding A Column So It Does Not Receive Pastes?
Got a spreadsheet that people are using to enter rates of return for bond funds. Column 1 is the name of the fund. Columns 2 through, say, 250 are returns for a given date (i.e. the column headers are dates). Users of the sheet are entering data partially by pasting blocks of numbers from other sources which do not include Saturdays and Sundays. The sheet in question, however, has columns for *all* dates in a range - including weekends - and I'd like to keep it that way. First thing that occurred to me was to hide the weekend columns, but when I tried a block paste, some of the numbers went into hidden columns as if they were visible. I *could* just not create columns for weekends, but for various programming reasons it would be easier and less complex to have them there. Anybody been here? Is there a way to make a column invisible not only to the user's eye but also to the paste process?  SignaturePeteCresswell try this idea Sub hideweekenddays() Columns.Hidden = False lc = Cells(1, Columns.Count).End(xlToLeft).Column For i = lc To 1 Step -1 If Weekday(Cells(1, i)) = 1 _ Or Weekday(Cells(1, i)) = 7 _ Then Columns(i).Hidden = True Next End Sub SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > Got a spreadsheet that people are using to enter rates of return for bond > funds. [quoted text clipped - 26 lines] > also to > the paste process? Per Don Guillett: >try this idea >Sub hideweekenddays() [quoted text clipped - 6 lines] >Next >End Sub What I need is for the hidden columns not to receive data when the user pasts in a range. SignaturePeteCresswell Lock the cells in those columns and protect the worksheet??? > Per Don Guillett: > >try this idea [quoted text clipped - 12 lines] > -- > PeteCresswell SignatureDave Peterson Per Dave Peterson: >Lock the cells in those columns and protect the worksheet??? But then the user cannot type anything into the cells, right? I want the user tb able to type stuff into the cells and/or put formulas behind them - yet have the sheet protected so they can't insert columns/rows or move columns/rows around. SignaturePeteCresswell Lock the cells in the columns that are hidden and unlock the cells that can be changed. Then protect the worksheet. And if you're using xl2002+, make sure you don't allow the user to insert/delete rows or columns. > Per Dave Peterson: > >Lock the cells in those columns and protect the worksheet??? [quoted text clipped - 6 lines] > -- > PeteCresswell SignatureDave Peterson Per Dave Peterson: >Lock the cells in the columns that are hidden and unlock the cells that can be >changed. Then protect the worksheet. Mea Culpa. That was in your previous post but I didn't pick up on the idea that I'd only be locking the hidden cells. Gonna give it a try this afternoon. Thanks. SignaturePeteCresswell
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.