Form Summarys

I want to know if i can write up my form on excel and then on anothe
sheet it summarizes it so i have a log of all the forms i send out bu
each time i create a new form can it create a new summary underneat
the old one ??

Many Thanks:D
Ben

--
Message posted from http://www.ExcelForum.com

0
1/3/2004 12:01:40 PM
excel.misc 78881 articles. 5 followers. Follow

14 Replies
528 Views

Similar Articles

[PageSpeed] 12

Does this mean that you have certain cells that you want to track?

If yes, then you could run a macro when you're done with the form:

Option Explicit
Option Base 0
Sub testme01()

    Dim myCellAddresses As Variant
    Dim FormWks As Worksheet
    Dim LogWks As Worksheet
    Dim nextRow As Long
    Dim iCtr As Long
    
    Set FormWks = Worksheets("formsheet")
    Set LogWks = Worksheets("logsheet")
    
    myCellAddresses = Array("a1", "b9", "c22", "e14")
    
    With LogWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        For iCtr = LBound(myCellAddresses) To UBound(myCellAddresses)
            .Cells(nextRow, iCtr + 1).Value _
                = FormWks.Range(myCellAddresses(iCtr))
        Next iCtr
    End With
    
End Sub

I used two worksheets (fromSheet and LogSheet).  I wanted the values in
a1,b9,c22,e14 to be kept in the log sheet in the next row (columns A:D).

(adjust names and addresses to match your form.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

"cassy01 <" wrote:
> 
> I want to know if i can write up my form on excel and then on another
> sheet it summarizes it so i have a log of all the forms i send out but
> each time i create a new form can it create a new summary underneath
> the old one ??
> 
> Many Thanks:D
> Benn
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/3/2004 4:21:42 PM
This Doesnt Make Sence Im affraid, so if you want i can send it directly
to you ? just send an email to bc1987@ntlworld.com

But if you cant can you try and explain it to me more simply. Thanks

My sheet1 is called Form
sheet2 is called Summary

i want cells : C11, B9, F9, B17, F17, C19, F13 in that order on sheet 1
to be put on sheet2 one cell in each column A,B,C,D,E,F,G

Many Thanks:D
Benn


---
Message posted from http://www.ExcelForum.com/

0
1/3/2004 6:45:28 PM
Is it the macro itself that makes no sense or the stuff that the macro does?

You can try this version.  Notice the changes in the worksheet name and the
addresses that you want copied.  (That's all I did.)  (And I just changed the
name of the macro to something more meaningful.)

Option Explicit
Option Base 0
Sub SaveMyValues()

    Dim myCellAddresses As Variant
    Dim FormWks As Worksheet
    Dim LogWks As Worksheet
    Dim nextRow As Long
    Dim iCtr As Long
    
    Set FormWks = Worksheets("form")
    Set LogWks = Worksheets("Summary")
    
    myCellAddresses = Array("C11", "B9", "F9", "B17", "F17", "C19", "F13")
    
    With LogWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        For iCtr = LBound(myCellAddresses) To UBound(myCellAddresses)
            .Cells(nextRow, iCtr + 1).Value _
                = FormWks.Range(myCellAddresses(iCtr))
        Next iCtr
    End With
    
End Sub

Again, you may want to read David McRitchie's notes if macros are new to you.


Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like:   VBAProject (yourfilename.xls)  

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
click on Tools|macro|macros...
click on the macro name (savemyvalues)
and then click run.

Don't forget to save the workbook--so you don't have to do the setup again!

"cassy01 <" wrote:
> 
> This Doesnt Make Sence Im affraid, so if you want i can send it directly
> to you ? just send an email to bc1987@ntlworld.com
> 
> But if you cant can you try and explain it to me more simply. Thanks
> 
> My sheet1 is called Form
> sheet2 is called Summary
> 
> i want cells : C11, B9, F9, B17, F17, C19, F13 in that order on sheet 1
> to be put on sheet2 one cell in each column A,B,C,D,E,F,G
> 
> Many Thanks:D
> Benn
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/3/2004 8:47:15 PM
thanks the second bit worked better for me the one where you just paste
it but i have one problem:

when i click on run macro it replaces the summary page so like when 
click on run macro it places all my form into the other page in th
column then i save it, then i write another form then click run macr
and it replaces it !!! i dont want it to be replaced i wan the nex
form summary to go underneath the old one so i keep a summary or th
forms i create.

Many Thanks:D
Ben

--
Message posted from http://www.ExcelForum.com

0
1/3/2004 10:31:55 PM
Actually, it uses column A to get the nextrow with this line:

nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

That +1 drops it down one row.  But if C11 is sometimes blank (and it's mapped
to column A), it could overwrite existing data.

Is there any column that always has data in it--the user can't leave that cell
blank?

If yes, then change that "A" to that column in the formula above.

If no, then maybe this'll work.  It uses the same technique to find the last row
as hitting ctrl-End does if you did it manually:

Option Explicit
Option Base 0
Sub SaveMyValues()

    Dim myCellAddresses As Variant
    Dim FormWks As Worksheet
    Dim LogWks As Worksheet
    Dim nextRow As Long
    Dim iCtr As Long
    Dim dummyRng As Range
    
    Set FormWks = Worksheets("form")
    Set LogWks = Worksheets("Summary")
    
    myCellAddresses = Array("C11", "B9", "F9", "B17", "F17", "C19", "F13")
    
    With LogWks
        Set dummyRng = .UsedRange  'try to reset lastcell
        nextRow = .Cells.SpecialCells(xlCellTypeLastCell).Row + 1
        For iCtr = LBound(myCellAddresses) To UBound(myCellAddresses)
            .Cells(nextRow, iCtr + 1).Value _
                = FormWks.Range(myCellAddresses(iCtr))
        Next iCtr
    End With
    
End Sub




"cassy01 <" wrote:
> 
> thanks the second bit worked better for me the one where you just pasted
> it but i have one problem:
> 
> when i click on run macro it replaces the summary page so like when i
> click on run macro it places all my form into the other page in the
> column then i save it, then i write another form then click run macro
> and it replaces it !!! i dont want it to be replaced i wan the next
> form summary to go underneath the old one so i keep a summary or the
> forms i create.
> 
> Many Thanks:D
> Benn
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/3/2004 11:40:58 PM
ok right, all the cells are always filled in so i will leave it so that
it can go in Column A but i tried to copy the above formula but it
wouldn't let me ?? do i have to change anything ??

Many Thanks
Benn


---
Message posted from http://www.ExcelForum.com/

0
1/4/2004 8:58:31 AM
ignore my last post i have fixed the problem, but, i have one othe
little problem i am going to insert some new formulas in columns G: 
so therefore the formulas above wont work properly so, i would like :

C11 of the "form" sheet to go in column A in the "summary" sheet.
B9 into column B
F9 into column C
B17 into column D
F17 into column E
C19 into column G
F13 into column O

if that is possible ??

Many Thanks :D
Ben

--
Message posted from http://www.ExcelForum.com

0
1/4/2004 10:50:18 AM
I'm not sure which way you determined the last row.  I modified the "ctrl-End" 
version.


Option Explicit
Option Base 0
Sub SaveMyValues()

    Dim myCellAddresses As Variant
    Dim myColumnLetters As Variant
    
    Dim FormWks As Worksheet
    Dim LogWks As Worksheet
    Dim nextRow As Long
    Dim iCtr As Long
    Dim dummyRng As Range
    
    Set FormWks = Worksheets("form")
    Set LogWks = Worksheets("Summary")
    
    myCellAddresses = Array("C11", "B9", "F9", "B17", "F17", "C19", "F13")
    myColumnLetters = Array("a", "b", "C", "d", "e", "G", "o")
    
    If UBound(myCellAddresses) <> UBound(myColumnLetters) Then
        MsgBox "design error! & vblf _
                "Make the number of cells match the number of colums!"
        Exit Sub
    End If
    
    With LogWks
        Set dummyRng = .UsedRange  'try to reset lastcell
        nextRow = .Cells.SpecialCells(xlCellTypeLastCell).Row + 1
        For iCtr = LBound(myCellAddresses) To UBound(myCellAddresses)
            .Cells(nextRow, myColumnLetters(iCtr)).Value _
                = FormWks.Range(myCellAddresses(iCtr))
        Next iCtr
    End With
    
End Sub

"cassy01 <" wrote:
> 
> ignore my last post i have fixed the problem, but, i have one other
> little problem i am going to insert some new formulas in columns G: N
> so therefore the formulas above wont work properly so, i would like :
> 
> C11 of the "form" sheet to go in column A in the "summary" sheet.
> B9 into column B
> F9 into column C
> B17 into column D
> F17 into column E
> C19 into column G
> F13 into column O
> 
> if that is possible ??
> 
> Many Thanks :D
> Benn
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/4/2004 3:08:46 PM
im sorry but this doesnt work ! Do you know of any other way ??

Many Thanks
Ben

--
Message posted from http://www.ExcelForum.com

0
1/4/2004 4:12:45 PM
No.

"cassy01 <" wrote:
> 
> im sorry but this doesnt work ! Do you know of any other way ??
> 
> Many Thanks
> Benn
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/4/2004 4:32:53 PM
What didn't work?

It worked ok for me.



Dave Peterson wrote:
> 
> No.
> 
> "cassy01 <" wrote:
> >
> > im sorry but this doesnt work ! Do you know of any other way ??
> >
> > Many Thanks
> > Benn
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
> 
> --
> 
> Dave Peterson
> ec35720@msn.com

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/4/2004 4:36:32 PM
i have altered the cells so that they are all together but everytime i
click Run Macro it goes underneath all my formulas but i want it to go
along side the other formulas is this possible ??

Many Thanks
Benn


---
Message posted from http://www.ExcelForum.com/

0
1/4/2004 4:39:26 PM
no it works but now i have entered another formula :

=IF($G4="C1",1,IF($G4="C2",2,IF($G4="C3",3,IF($G4="C4",4,IF($G4="C5",5,IF($G4="C6",6,IF($G4="C7",7,0)))))))

But when i click run macro it goes to the next empty row but i want i
to go on the same row as the above formula

Many Thanks
Ben

--
Message posted from http://www.ExcelForum.com

0
1/4/2004 4:48:16 PM
Where is the formula?

And you always want it to go on the same row as the formula?  Won't that mean
that you only get to keep track of one set of values?

"cassy01 <" wrote:
> 
> no it works but now i have entered another formula :
> 
> =IF($G4="C1",1,IF($G4="C2",2,IF($G4="C3",3,IF($G4="C4",4,IF($G4="C5",5,IF($G4="C6",6,IF($G4="C7",7,0)))))))
> 
> But when i click run macro it goes to the next empty row but i want it
> to go on the same row as the above formula
> 
> Many Thanks
> Benn
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/4/2004 6:59:08 PM
Reply:

Similar Artilces:

Creating a form for a Password
I have created a Password form that will secure another form. Once the password is entered it will open the form that has the list of reports. On the form I have a text box that accepts the user's password and I have 2 command buttons one is OK and the other is CANCEL. Can anyone tell me how to make these buttons work to open the form of reports? On Wed, 10 Oct 2007 13:52:00 -0700, mrsgwen wrote: > I have created a Password form that will secure another form. Once the > password is entered it will open the form that has the list of reports. On > the form I have a text b...

Selecting variable records from a form
I've created a checklist form and would like to create a report that demonstrates only the items in the form that have data. SH -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200708/1 ...

Report can't print form field
My report heading contains a text box with the following data source: ="Transactions since " & Forms!frmPrintTran!txtDate The on-screen preview shows the date fine, but on printing, this textbox displays #Name? The form remains open during print. Happens with both Access 2002 and 2007, DB structure A2000. What gives here? Thanks. -- TedMi This usually happens when there is code in the On Close of the report that closes the form. -- Duane Hookom Microsoft Access MVP "tedmi" wrote: > My report heading contains a text box with the following data source: > ...

Forms Check Box cell link
Excel 2003 I am using a check box created with the Forms menu. I created a box over cell D4. Using the Format Control box, I linked the check box to cell D4. I want to copy the check box in cell D5, D6, D7,.... and I want the cell link to integrate in the same mannor; check box in cell D5 should link to cell D5, and so on. Is there a way to accomplish this short of going into the format control box and manually editing each cell link after I have copy and pasted several check boxes? I have NOT entered the link using an absolute cell referense. I got the idea for this from Dav...

Customizing Forms Wizard
It is possible to control or customize details of the way the wizards creates objects? For instance, we have internal naming standards for controls and I would like the wizard to name each control according to our standard. I know I can use a form template for the forms created without wizards, but what about controlling the creation of the ones done via wizards too? Thanks in advance, See if this helps: http://support.microsoft.com/kb/151218/EN-US/ Román Valoria wrote: >It is possible to control or customize details of the way the wizards creates >objects? > >For ...

Form Menu : Check Box
I am trying to enter 300 or so check boxes onto and Excel spreadsheet. I find that I can link the 'return' cell using the right click / Forma Control / Control tab / Cell Link option but I need to do this for eac box ! Is there a way around this ? Whereby I can create one Check Box an then drag or copy it down -- jagstirlin ----------------------------------------------------------------------- jagstirling's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=110 View this thread: http://www.excelforum.com/showthread.php?threadid=50634 Maybe you could jus...

Access-Forms-VB links
After years in software development/database programming mostly on Unix/Oracle I'm tasked to change some Access forms to use redesigned tables. I'm having trouble with the relationship between forms and VB. For example I'm looking at some VB code that frequently references the Me class which I assume to be the default reference to the current (form) class (like this in C++), but I can't see how/where the class is actually defined. Can someone please provide some links that provide concise explanations of the relationship between Access and VB. My googling has only turned up pag...

can a form display current table field names?
Hello, I need to create a form that will display the current field names resulting from a make-table querythat can change from audit to audit. This way the user is always aware of the "current" field names in their dataset. Currently, a form displays the structure from a linked (text formt) table that I have to manually export every time the make-table query changes and I'd like this to be generated from a macro or from code. Any Ideas? I thank you, John_Lehmus Maine, USA This article illustrates how you can list the field names, types, and descriptions for a table: ...

Sinking MS Access form events from MFC console app
Hi All, I'm trying to sink MS Access form events using technique described in <<HOW TO: Handle Events for Excel by Using Visual C++ .NET (http://www.kbalertz.com/Q309301/Handle.Events.Excel.Using.Visual.aspx)>> Of course I did do all the needed code substitutions etc. All works rather well, I mean I'm getting to MS Access form IConnectionPointContainer etc. But the last code line - hr = m_pConnectionPoint->Advise(m_pAppEventListener,&m_dwConnectionCookie); doesn't work and returns hr = -2147220990. Here is MS Access form events interface ID I use: const II...

Monthly Summary of days of the week ?
Greetings all, I hope someone can help me with this issue. I have one spreadsheet with worksheets for each month for the last 18 months. Each worksheet has column A being the date eg 1st -31st January 2009 Col B has the formula to set the actual day of the week - 1/1/09 = Thursday Then I have numbers in columns B to G for incomings etc and outgoings then a net figure Now I have all sorts of averages per day per month per year but would like to have an automatic calculation so I know what day of the week (e.g Tuesday ) is as a percentage of the weeks $$'s Therefore knowing the best and ...

Linking Form and Report
Good evening, I have a Main Entry Form for the entry of installation data. I have a Report set up to be used as an Audit tool. I would like to have the Audit Report pop up for review when the Save & Close button is depressed. The Form and the Report are linked by Folio No, unfortunately I have not been successful, when you push the Save & Close the form opens but its blank. I have checked the underlying tables and the data seems to be there. Is there something unusual about trying to attach a Report to a Form at all. Private Sub btnSaveRecord_Click() On Error GoTo Err_btnSa...

Passing Values between forms
I am trying to link to forms. I have a button on the main form (frmCompanies) that needs to open another form when a button is clicked. The new form that opens is frmScheduledActivities. When it open it needs to check to see if there is an existing record (scheduled activity) for the company and if there is no scheduled activity be set to add a new record. When the button is clicked on the main company form (on the on click event) this code runs: Private Sub cmdCallBack_Click() On Error GoTo Err_cmdCallBack_Click Dim stDocName As String Dim stLinkCriteria As String...

Display Query from form in Foreground
I have a form upon which the user selects display, filter and sort fields. He then clics the submit button and a query is built and then displayed with the following code: DoCmd.OpenQuery "Queryname", acNormal, acEdit The query works but shows up in the background and cant be clicked on until the form is closed. The idea was to create a form on which the user could do adhoc queries until he gets it right and then click another button to do the Excel extract. Is there a way to show the query in the foreground? On Tue, 22 Jan 2008 17:54:27 -0800, rmcompute wrote: > I ha...

Return Value from Form
I need to return a value from a form displayed modally. Many years ago, if I used VB classic, I could create a public method that took care of any arguments, displayed the form modally, and then returned appropriate return values. Something like this: Public Function GetValue(Args As Integer m_Args = Args m_Result = DefaultResult Show vbModal GetValue = m_Result End Function However, it appears Access VBA doesn't support this. For one thing, there's no Show method (and so it certainly can't have a modal argument). I like this approach because ...

User Form Security Question
Hi, I'm creating a form, which onces completed transfers the data over to the spreadsheet. But the guys in work don't really pay much attention to what they have entered , so i was wondering is there any way to make the user double check every entry? I was thinking, once the user clicks ok, a msgbox would appear giving the user the value they entered, click ok if correct or cancel to amend. Is there a easy way to do this? Cheers. -- harpscardiff ------------------------------------------------------------------------ harpscardiff's Profile: http://www.excelforum.com/mem...

I did do an update today form Microsofts update page #2
If I can remember correctly, it was 2 new items that came out today for the OS. Could that have casued this. But it worked Ok afterwards. Nothing else installed in the last week. ...

Automating a Summary Tab
I have a workbook that I need a summary page that will breakout 2 different tabs and subtotal it Is there a way that if 1 tab holds Part #, Desc, and Price Another tab holds Total Price for the type of project it is So that would be: Model, List Price In a Summary tab I would like to show Item # (this would automatically start numbering each row) depending on how many choices they made in the other tabs Part # Description Model Length of term # users List Price They could choose anywhere from 2 parts to 15 parts how do I get it to automatically populate for what they chosen in this new...

How to I stop the form from resetting
I have a table linked to the header of my form. The table has for records. Here's what happens. When I choose a record (say record 3) record 1 ALWAYS prints. The form is meant to be printed out. Can someon please tell me how I can get the record I choose to print out? Thanks in advance!! :o) Although it is possible to print a form, it is not advised. You will get better results if you create a report and print the report. -- Dave Hargis, Microsoft Access MVP "Printing only filtered data in a form" wrote: > I have a table linked to the header of my form. The table h...

Pass Through query with criteria on a form
I have a Pass Through query that looks like this. select Location,City,State,Country,Pings from (select sd_term_name_loc as 'Location', sd_term_city as 'City', sd_term_st as 'State', sd_term_cntry as 'Country', count(sd_key)as 'Pings' from detail (nolock) where -- ***** The dates below refer to the date and time at which the transaction was loaded to PRM. -- ***** -- ***** The number of pings which have occurred BETWEEN those dates/times will be counted. -- ***** -- ***** Enter the date/time in the format '09/16/2007 14:00:00.000...

issue with serial port on module, updating form
Hello, Ive created a serial port instance on a module. In the same module I have the sub data_received. Now I'd like to update a control in a form, how can I do it? Thanks in advance You need to use Control.BeginInvoke or .Invoke to update the UI via a Delegate. You can download the VB2005 Terminal example from my website, which illustrates this. Dick -- Richard Grier, Consultant, Hard & Software 12962 West Louisiana Avenue Lakewood, CO 80228 303-986-2179 (voice) Homepage: www.hardandsoftware.net Author of Visual Basic Programmer's Guide to Serial Comm...

How can I put photos in Microsoft Access Form
I used the information BobLarson said in putting photos in Microsoft Access Form but it's not working. My computer keeps asking for Form1, which I deleted because nothing was happening the way I wanted it to. What I would like to know is there a online video that can show how you can do this procedure. And how can I get my computer stop asking for Form1. "Helen" <Helen@discussions.microsoft.com> wrote in message news:8E015B36-D605-428D-9275-C95DAC1DC3AE@microsoft.com... >I used the information BobLarson said in putting photos in Microsoft Access > For...

This form Requires Word
Thanks gang, I am reasonably up to snuff on Outlook and have used it for as long as it has been around. I am running Windows 2k Pro and Office 2k Pro and Outlook is configured to use Word as the default email editor. It does so quiet well both composing and reading email, except when email is receives from one particular person, one out of hundreds. This lady <the sender> is also using Outlook 2k pro and windows 2k pro and word as default editor. When I receive mail from her and try to open it I receive the message "This form requires Word as your email editor, but Word is eith...

Multiple Record Inputs with one Form
I'm programming with SQL and can't seem how to figure out how to send more than one record to the table with one form. You've described a "how" ... as in how you are trying to do something ("send more than one record to the table with one form"). Now, "why" ... as in what will having multiple records generated from a single form allow you to do (i.e., business need)? This isn't idle curiosity ... if we knew more about the underlying business need, we might be able to offer alternate approaches. Regards Jeff Boyce Microsoft Office/Access ...

Attaining Form for Excel
We are having some difficulty in attaining 'Forms' on Excel 2007. We have been directed to 'Down loads' and then attempt to down load 'Exel 2002 Add- in:Template Wizard with data Tracking. So far we have been unable to find any wizard after the down load. Please help -- thanks Griff Hi, what do you mean by "attaining form"? Please give a more complete description of what you are trying to do, disregarding the download of the wizard. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Griff" wrote: > We are having...

Listbox selected freezes the form
I'm using the following line in the form_open to select the first item in the list which susequently selects data in the second listbox: Me.lstDate.Selected(0) = True However the whole form freezes and nothing works? Any ideas? Many thanks ShariS "ShariS" <u40676@uwe> wrote in message news:7e4b3831704cd@uwe... > I'm using the following line in the form_open to select the first item in > the > list which susequently selects data in the second listbox: > > Me.lstDate.Selected(0) = True > > However the whole form freezes and nothing works? Any i...