Hi, In EXCEL if it possible to c 2

[wuwang]

Hi,

In EXCEL if it possible to create a form jsut like Visual Basic does and with buttons on the form?


Thank you

 

[DaleWatson123321]

wuwang,

To get you started, you should FIRST add the "Control Toolbox" to your toolbars at the top of your screen. To do this, right-click on the toolbar area, choose "Customize", and under Toolbards, choose "Control Toolbars" and drag it to the top of your screen.

On this toolbar, there is an "Image" icon (3rd from the right). Drag this to your worksheet and "size it" for the size of the form you wish to create. You'll notice on the "formula edit line" the following =EMBED("Forms.Image.1","&quot.

You can then drag other "components" for your form from the same toolbar. With each of the components you add, right-click on them and choose "Format Control" to set up and modify the options for capturing the required data.

Hope this gets you started.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

Hi,
In Excel and other MS Office applications there is a subset of VB called VBA (Visual BASIC for Applications). So the answer to your question is that you can create a form called a UserForm with controls like buttons and text and list boxes etc. BUT, there is less functionality than VB.
You can start the VBE in Excel by alt+F11 and then go about inserting the objects you want and withing VBA code to your heart's content.
Hope this helps.. Skip,
metzgsk@voughtaircraft.com

 

[DaleWatson123321]

wuwang,

Skip's route is obviously the better option if you want to get into creating Forms in a more serious way, as it gives you complete access to numerous Form settings.

A STAR for Skip.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

Dale,
I never knew that you could drag an Image onto a Sheet and create a Form that way. I have used controls directly on a sheet, but that is STAR-tling!

Thanx! Skip,
metzgsk@voughtaircraft.com

 

[wuwang]

Thank you, everybody.

 

[wuwang]

Hi,

I have another question. My customers use ACCESS to run the report and export these bad records to the EXCEL spreadsheet. Different customers may have different report output.

I plan to create a standard form using EXCEL with standard columns name on it and have customers send their report to the standard EXCEL form and automatically find the right column to fill out.

For example, customer A wants to update LAST_NAME, so he can export the bad LAST_NAME output to my standard EXCEL form to the LAST_NAME column.

customer B wants to update ADDRESS, so he can export the bad ADDRESS output to my standard EXCEL form to the ADDRESS column.

If it is possible to do it?

Thank you for your help

 

[SkipVought]

Hi,
Rather than settin up a table with those headings in Excel, I wold suggest an Error Table with at least 2 headings...
1. The Data value and
2. The Heading value
You might also want date and user id.

That would be my humble approch. Skip,
metzgsk@voughtaircraft.com

 

[min460120]

Thank you for your input but it is not the way we plan to do. There are over 30 customers send in reports. Some of them put the data that need to be updated in EXCEL spreadsheet directly, but some of them need to run report first in ACCESS then export to EXCEL.

Since I need to gather these requests(records) and do something more in EXCEL, that's why I like to know if it is possible to have customers who run ACCESS to send their report to the standard EXCEL form and automatically find the right column to fill out.

Thanks again

 

[SkipVought]

Well it is possible...
Here's a way to find the column for a particular HeadingName

Function WhatColumn(sHeadingName As String, _
    lRowHeading As Long, iColHeadingStart As Integer) As Integer
    Dim rngHeading As Range
    Set rngHeading = Range(Cells(lRowHeading, iColHeadingStart), _
        Cells(lRowHeading, iColHeadingStart).End(xlToRight))
    For Each Heading In rngHeading
        If Heading.Value = sHeadingName Then
            WhatColumn = Heading.Column
            Exit Function
        End If
    Next Heading
    WhatColumn = 0
End Function

Of course, if your Excel table starts in row 1, column 1, you can modify the functoin to take only the heading name.
Then the next empty row in the table would be...

Function WhatRow(lRowHeading As Long, _
            iColHeadingStart As Integer) As Long
    With Cells(lRowHeading, iColHeadingStart).CurrentRegion
        WhatRow = lRowHeading + .Rows.Count
    End With
End Function

Hope this helps Skip,
metzgsk@voughtaircraft.com