Transforming a worksheet's functionality?

Hello Everybody.  How is your Friday?

Okay, is it possible, using a recordable Macro or VBA, to change a 
worksheet's functionality?

For instance, I have a workbook with one Formatted Data Entry sheet and one 
No-Format Archive sheet that stores all the raw data (no formulas) for future 
retrieve.  Is it possible to change the Data Entry sheet from user input mode 
to archive retrieval mode?  

In data entry mode, Column D and G are blank to allow for user input.  In 
archive mode, Column D and G would need formulas which grabbed data from the 
corresponding cells on the archive sheet.

The solution should also be able to lock/unlock those cells accordingly.

Can this be done?

Thanks for your time and help.

Arlen    
0
Arlen (19)
2/4/2005 4:51:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
431 Views

Similar Articles

[PageSpeed] 16

I'm not sure, but how about a slight change.

On the archival sheet (values, not formulas), you put a button that runs a
macro.

That macro takes the values out of the activerow (user selects the row before
hitting the button), then populates the Input sheet.

The macro would be used instead of formulas.  So instead of the user typing
them, the macro would "type" them.



Arlen wrote:
> 
> Hello Everybody.  How is your Friday?
> 
> Okay, is it possible, using a recordable Macro or VBA, to change a
> worksheet's functionality?
> 
> For instance, I have a workbook with one Formatted Data Entry sheet and one
> No-Format Archive sheet that stores all the raw data (no formulas) for future
> retrieve.  Is it possible to change the Data Entry sheet from user input mode
> to archive retrieval mode?
> 
> In data entry mode, Column D and G are blank to allow for user input.  In
> archive mode, Column D and G would need formulas which grabbed data from the
> corresponding cells on the archive sheet.
> 
> The solution should also be able to lock/unlock those cells accordingly.
> 
> Can this be done?
> 
> Thanks for your time and help.
> 
> Arlen

-- 

Dave Peterson
0
ec357201 (5290)
2/4/2005 10:57:23 PM
Reply:

Similar Artilces:

Rename the worksheet
Is there a possibility to rename the worksheet without using the mouse-rename procedure? I mean using the keyboard only??? Thanks. -- praveen_khm ------------------------------------------------------------------------ praveen_khm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30364 View this thread: http://www.excelforum.com/showthread.php?threadid=502191 Dear Praveen, Use Alt+O+H+R to rename the worksheet. Regards, Selvarathinam. praveen_khm wrote: > Is there a possibility to rename the worksheet without using the > mouse-rename procedure? I me...

Time functions #2
Hi, I have to do some time calculations and I have a formula that outputs in minutes and I have to subtract a standard time to this number of minutes. For example, I have 8:00 AM in one cell and 20.68 minutes on an other cell. The output that I am expecting is 7:39 AM. Does anybody know how to bring an output like that by subtracting the minutes? Please help. Thanks With 8:00 AM in A1 and 20.68 in B1, use this formula =A1-B1/(24*60) Why does it work? Because Excel stores time as a fraction of a day. We convert minutes to days by dividing by 24*60 (24 hr/day and 60 min/hr) You could just u...

Change background / color of chart worksheet?
Need steps to change the color or backgound of the worksheet that is a chart. Have multiple tabs in workbook that are just the charts. Only option for background is to use Format-Sheet-Background. That changes background of chart, but not the grey areas around the chart on the chart tab. You need to select the Chart Area (what you call the, "grey areas around the chart") and format that. There are 2 separate, "parts" to a chart:- 1. The Plot Area (which you seem to have formatted OK) and the 2. Chart Area. You need to select each of them to format th...

Adding functionality to MS Outlook Express
Hi, I am looking for help to integrate anti-spam open source programs with MS Outlook Express :- - SpamPal - http://www.spampal.org/ - Spamato - http://www.spamato.net/ The problem is to provide a library that will facilitate this. I have read the MSDN Messaging and Collaboration documentation which gives the ability to get notifications of new messages and to access message folders :- http://msdn2.microsoft.com/en-gb/library/ms709546.aspx The next need is to add a toolbar (with buttons for SPAM/NOSPAM) and to detect what messages are selected in the message window. The MSOE...

Inserted rows, now need Counta function
Hi I've got a data dump. I've figured out how to insert a blank row after a change in name in column A and insert "Total" - so... bill.... bill.... bill Total - bob.... bob.... bob.... bob Total - What I need in column C next to total is to insert the COUNTA function for each person. Any ideas? cheers You say your code inserts a blank row after a change in name and inserts "Total", but your example doesn't show this. Can you post the code you are using to do your "insert"? It will probably be easier to handle the ...

If Function Possibilities
Is there a way to set up in a cell A1, something like... If the formula used in cell B1 is "here I would type the actual formula in," then display the words "works," else display "mistake" ? -- rheller ------------------------------------------------------------------------ rheller's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24081 View this thread: http://www.excelforum.com/showthread.php?threadid=377045 That's a pretty cryptic post. Do you mean: In A1: =IF(B1=n,"Works","Mistake") where 'n...

Start a file at a defaulted worksheet/cell?
Howdy, Having a few users to a file, can I have the file open to a default worksheet/cell as I have directions I want them to read before playing... Regards, Kevin Kevin, use something like this, put in thisworkbook code Private Sub Workbook_Open() Sheets("Sheet2").Select Range("C3").Select End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "...

copy data from one worksheet to identical sheet in different workbook
I want to essentially sync the data between 2 identical worksheets in different workbooks. Essentially, I want to import and replace data in the second sheet with data from the first, is there a simple way to do this without deleting the worksheet and then copying the new sheet into the workbook? Thanks You can right-click on your source-sheetname tab, the shortmenu will reflect Copy/MoveSheet.... Select it and make 2 other choices: 1) Check to Create Copy box at bottom 2) From dropdown box at top of screen select an existing open workbook or select new-workbook. OK HTH "a...

Writing Access functions
I am an old FoxPro programmer (but have been out of programming for many years) and I have been through the Access basics that I have been able to find on line. So I can create tables, forms enter data the basics. I am learning VBA no problem there. Now I want to write some functions. I would like to write one function that is passed an ID and returns the last name of the person having that ID. I would like to write this function in two ways: 1. Connect to a table in my DB find the ID and return the name 2. Have the function perform a query that returns a record set from which t...

reading file name from folder function
I am using Access 97. Just wonder if there is a fuction which can read all the file name from a folder and put it to a column in a table. Please let me know. Thanks. Hi. >I am using Access 97. Just wonder if there is a fuction which can read all >the file name from a folder and put it to a column in a table. Please see the getFileInfo( ) function in the tip, "How to compare the contents of two directories," on the following Web page for an example: http://www.access.qbuilt.com/html/vba1.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http...

Function Keys in Excel
Can someone please tell me what the F2 key does in Excel 2000? I have a column of data which has been copied from Access 2000 into an Excel 2000 sheet. It is numeric data and when I use the "Countif" function on this data, it doesn't calculate. However, if I press the "F2" function key in any of the cells, something happens to the data so that the "countif" function recoginises it. Can anyone explain what is happening here? Thank you. -- LPS F2 allows you to edit directly in the cell, do you have calculations set to automatic under tools>calcul...

Excel macro for copying range to another worksheet
On a monthly basis, I would like to copy the completed range (varies from month to month) of a database (Sheet1) to a master list (Sheet3). Once the data has been copied I intend to manually delete the entries of Sheet1 and start anew for the new month =96 for eventual transfer to Sheet3. The idea is to copy each month=92s data at the bottom of the previous months=92 (Sheet3). I followed Excel=92s record macro command but the macro I ended up with is not capable of placing the new data at the bottom of the existing one; it simply keeps overwriting the previous entry. Unfortunately, I don=92t...

worksheet appearance
Hey, I create worksheets in VBA, then delete them again and create them again. One worksheet always stays in my workbook, but when I create such a new worksheet with add it appears the first in line. I want it to appear it after the worksheet stat is always visible. How can I solve this? From XL/VBA Help: > Add Method (Worksheets Collection) > > Creates a new worksheet. The new worksheet becomes the active sheet. Returns > a Worksheet object. > Syntax > expression.Add(Before, After, Count, Type) > expression Required. An expression that returns a Worksheets object....

faq: type of CArray as function parameter create errors
Hi, class CMyPoint { public: long x; long y; CArray<int,int&> m_intSegmentNo; CMyPoint(); virtual ~CMyPoint(); CMyPoint(const long &xx, const long &yy); CMyPoint(const CMyPoint &cmpt); CMyPoint(const CPoint &cpt); CMyPoint& operator = (const CMyPoint &assmypt); CMyPoint& operator = (const CPoint &asspt); bool operator == (const CMyPoint &rmypt);//const CMyPoint &lmypt, bool operator != (const CMyPoint &rmypt);//const CMyPoint &lmypt, }; .... class CSegment { public: CList<CMyPoint,CMyPoint&> m_PointList; CSeg...

Function for convertion of leters doesn't work.
I'm using function LOWER to convert string i my table on small size but Access gives me a message that the function is not defined.Please, do you know another function? MS Access does not have a LOWER function. Try LCase() instead. -- Cheryl Fischer Law/Sys Associates Houston, TX "Stenli" <svet2000@seznam.cz> wrote in message news:10b201c3626e$b613a050$a301280a@phx.gbl... > I'm using function LOWER to convert string i my table on > small size but Access gives me a message that the function > is not defined.Please, do you know another function? ...

Report Writer Functions #2
Where can I find instructions and examples on the various report writer functions? I've installed the SDK but I don't see anything there. The Report Writer On-line manual (Help - Printable Manuals - Reporting - Report Writer) has a fairly good description of the functions in Chapter 6. Do you look there or is there a specific function that you did not find there for which you are searching? "Elaine" wrote: > Where can I find instructions and examples on the various report writer > functions? I've installed the SDK but I don't see anything there. Hi El...

Public Function not running
Hey, I have a public function that I call from a cell. The function is: Public Function QuitTime(strQuitTime As String) As Date Dim strHour, strMin As String Dim intHour, intMin As Integer Dim dtTime As Date strHour = Left(strQuitTime, 2) strMin = Right(strQuitTime, 2) dtTime = strHour & ":" & strMin dtTime = dtTime + Range("MainSheet!B2") ' this cell contains the value today() QuitTime = dtTime End Function Anyway, I run a vlookup on this array from another sheet and when ever i run that vlookup, it makes all the cells in th...

Question on "Value" function
In trying to use Excel to help solve a mathematical puzzle, I would like to calculate the result of applying the mathematical operator in one cell to the numbers in two other cells. Example: A1 contains 2, B1 contains +, C1 contains 3. In D1, create a formula that will give the result of 2 + 3, i.e. 5. I have tried (among many other attempts) =VALUE(CONCATENATE(A1, B1, C1)), but it gives me a #VALUE! error. What am I missing? Hi this is not possible without using VBA. try the following UDF: Public Function my_calculate(op1 As Range, operand As Range, op2 As Range) my_calculate = Ap...

Searching a Differnt worksheet in the same workbook.
So what I need to do is have a colum D that searches Sheet1, column E for a certain text string entered in a different sheet (either 2 or 3) column D. If they match I want the cell in column E, sheet 2 or 3 to return a text of "Found" and if there is no match I want the cell to be blank. I've been looking in these forums and have a feeling it can be done, I just can't figure out how. Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Dear Cre...

vlookup help linking data between worksheet
hi, I have a master list of students (about 200+ )in one worksheet. On the succeeding worksheets are the attendance for seminars. We have more than 20 seminars in a year. Because of the large no of attendees per seminar, I usually type out a list of the students who came for each seminar, so there will be 20 +attendance worksheets. Not all students will come every time and there are new ones for each session. To update on master list, I will sort each sheet by surname, print it out and type in separate column (date) for each session and typed "P" for present and &quo...

Excel VBA
Hi, Is there any way to freeze (prevent) a number of worksheets from recalculating when a VBA macro is running? I have a spreadsheet with about 10 worksheets, most of which are linked to each other in some way. One of my macros only needs to use the formulas (and related results) from about 7 of these, so the other 3 are updating all the time but since the results from these are not needed then they are slowing down my code. Is there some VBA function for temporarily disabling a worksheets calculation function? I would need my code to look something like this: Code: -------------------- ...

Linking items GREATER THAN O on another worksheet in the same Work
I have a workbook that has about four worksheets … I want to create a list of items that will automatically be included on worksheet tab 4 from Worksheet tab 1 if the quantity for the item listed on the first worksheet has been changed from 0 to any other number (i.e. 1 or 10 etc). I have a list of equipment with pricing on worksheet tab 1 and the list is long. Many of the items will have quantities of zero and will not increase as they will not be selected for the project being created. We have additional columns for cost, extended cost, labor hours etc. If the quantity changes for a...

Missing engineering functions
In an earlier version of Excel, I had "engineering functions" like =dec2hex and =hex2dec. Not, in Excel 2003, I see them in the "help files' but not in my list of available formulas. How do I get them to work in Excel 2003? Thanks in advance, Geoff Waters Glendale, CA Geoff, I don't have Excel 2003, but you should be able to go to Tools | Add-ins and check mark "Analysis Took Pak" in the list that appears. Regards, Jim Cone San Francisco, CA "Geoff" <grw888@hotmail.com> wrote in message news:zwfIc.299$Qu5.238@newsread2.news.pas.earthli...

Read Receipt Function in outlook
The read receipt option is no longer working in Outlook Small Business Edition for XP 2002. I have not made any recent changes to my outlook setup. Does anyone know how I can trouble shoot this? ...

Indirect and Address Functions to Determine Schedule #3
I am struggling with writing an Excel formula that I would greatl appreciate some help on. In theory this sounds quite simple. I have a table which shows th progression it takes, in months, to move from one 'level' to the next. Another larger table shows how a number of 'starting' individuals woul progress through their levels based on the progression table mentione earlier. e.g. [FONT=courier new]Progression Table[/FONT] ----------------------- Months Level One 1 Level Two 2 Level Three 3 etc. Duration Table # of People Jan Feb M...