PDA

View Full Version : Solved: Data Entry Control



maryam
01-10-2007, 03:29 AM
I have a user form in which I want to put a table to let the user input the data. Should I use a seprate input control or Is it possible to insert an excel sheet into the form? I tried Data Grid and MSFlex Grid Controls, but I can not input any data into the cells of these controls.

lucas
01-10-2007, 09:12 AM
Moved to the Excel help forum.

If you are trying to make an input form to add data to a reguar spreadsheet you can use textboxes, comboboxes, etc.

You can add a spreadsheet to a userform.
While viewing your userform in the vbe
right click on the toolbox and select additional controls
look for microsoft office spreadsheet.

maryam
01-10-2007, 07:21 PM
Are DataGrid and MSFlex Grid Controls for data entry? When I put them on the form and Run, I cannot input any data into the cells of these controls. I need to have a table for data entry. The data may come from the user or a database. What kind of control should I use?

lucas
01-10-2007, 08:08 PM
Hi maryam,
I think that DataGrid and MSFlex Grid Controls are visual basic controls for VB5 or VB6. This forum deals with VBA(Visual Basic for Applications) and I don't think those controls are available in VBA.

Are you working in Excel or are you creating a VB project? You mentioned an excel spreadsheet in your first post so I thought you must be working in Excel...?

maryam
01-10-2007, 08:51 PM
Hi Lucas,
I am working in VBA, from tools menu/ additional controls I can find Data Grid and MSFLex Grid controls. But As I worte I cannot input any data into the cells of these controls. Which control should I use to be able to let the user input data and also to be able to take the data from the database?



Thanks & Regards,
Maryam

lucas
01-10-2007, 09:12 PM
Ok...I got out of you that your working in VBA....I will assume it's excel but it would be nice to know.

So you wish to input data into a userform in excel and also be able to import data from a database? Sound right so far?

What kind of database? Access, Text file, csv, etc....

I don't find Data Grid or MSFlex Grid controls in my additional controls but there are a lot of controls there so....could you upload your workbook with these controls on a userform please?

Could you provide a sample of the database file. Also in your spreadsheet put an example of a sample of the data and how it should look.

malik641
01-10-2007, 09:12 PM
Hi Maryam, Welcome to VBAX! :)

Quick question. How come you want to even use the spreadsheet control (which is what I believe you're looking for, check out lucas' post [#2])? And are you working IN excel, or just want to your form to "feel" like it's excel and you're using another application, say, Access?

Could you provide a small example of what you are trying to accomplish exactly. Like a dummy workbook or something?

lucas
01-10-2007, 09:15 PM
Hi Joseph, glad you dropped in on this one.

maryam
01-10-2007, 10:34 PM
Yes, It is VBA in excel.
when you press Alt+F11 then insert a user form then tools menu/ additional controls/ Microsoft DataGrid Control or Microsoft FlexGrid Control
which contorls you mean I should upload.
my form is something like the pic I attached to this thread. I want ot make a table like the one which is in composition frame. The input data of this table should come either from the user( I mean user should be able to input data into or edit the data of the cells) or it comes from anther excel sheet in which we saved our data.
Thanks for your concern. please let me know if everything is clear now.



Best Regards,
Maryam

maryam
01-11-2007, 12:51 AM
How can I attach a file? the attach icon is not working.

maryam
01-11-2007, 01:40 AM
Dear Malik and lucas,
I cannot attach my file.Would you pls send me your your emails so that I can send it.

mdmackillop
01-11-2007, 04:01 AM
To attach a file, use Manage Attachments in the Go Advanced section.

malik641
01-11-2007, 06:24 AM
Hi Joseph, glad you dropped in on this one.
:)


Maryam,


when you press Alt+F11 then insert a user form then tools menu/ additional controls/ Microsoft DataGrid Control or Microsoft FlexGrid Control
which contorls you mean I should upload.

Neither. Do the same process, but add "Microsoft Spreadsheet Control XX.0" instead. This is what Lucas and I are talking about.

maryam
01-11-2007, 09:42 PM
Dear Lucas and MAlik,
I tried Microsoft spreedsheet control 11 and 10, but this is not what I want. My table has fixed number of columns and the spreadsheet doesn't look suitable for it. Still I cannot use the attached icon and I need to send a schematic of my form to you so that you can understand what kind of table I mean. Can you help me with this pls? what is managment attachments in the go advance section?



Thanks,
Maryam

mdmackillop
01-12-2007, 04:22 AM
Hi Maryam,
Instead of clicking Post Quick Reply, click Go Advanced

maryam
01-12-2007, 08:40 AM
Dear Administrator,
I just click on post reply, where is advance?

lucas
01-12-2007, 08:49 AM
......

maryam
01-12-2007, 08:54 PM
my picture file is a .BMP FILE, pait file and while I try to upload it from my computer, it gives upload of file failed. I can upload other files like pdfs. Please give me an email adress so that I can send the file and you can help me with this thread. Worksheet is not an appropriate control as it has many columns and it doeant look good. I need a control in which a user can input data so that data entry will be friendly and I dont need to put many text boxes, but I dont know why we cannot write in the cells of Data Grid and MSFlex Grid controls?!



Thanks,
Maryam

lucas
01-13-2007, 10:29 AM
Well...you could attach your workbook....with your data Grid etc. controls.....if it's too big(which is why your bitmap didn't work) you can zip it up.

We would just as soon see the workbook. Also your doing data entry.......it would help if in the workbook you show a few rows of how the data will look on the worksheet.

mdmackillop
01-13-2007, 11:17 AM
Here's a workbook with both grid types. I've not yet tried loading data to the forms.

Edit: There's a query re FlaxGrid with an example here
http://www.ozgrid.com/forum/showthread.php?p=174529

malik641
01-13-2007, 01:21 PM
Well, I can't figure out the DataGrid control. I only figured out how to add columns to it. But when I try to add rows VBA gives me an error telling me "Data member not found." :dunno

Anyway here's a small example of the MSFlexGrid control. I hope this will get the ball rolling. :thumb

mdmackillop
01-14-2007, 06:42 AM
Here's Maryam's image.

maryam
01-16-2007, 06:37 AM
So I don't get my question. Is there any table like control in which a user can input the data? I cannot input data directly into the cells of MSFlexGrid or DataGrid Controls:-(.

malik641
01-16-2007, 07:26 AM
I cannot input data directly into the cells of MSFlexGrid or DataGrid Controls:-(.
When you say "directly" do you mean by typing it in when the userform is open, or by code?

I know you can add the data by code into the MSFlexGrid control, I provided a sample of that in post #21.

I think you can add data to the DataGrid control, it's a setting on the control. I don't have the DataGrid control here at work, so I can't directly tell you which one it is. But it's something like "AddNew" and it's default is set to "False" so set it to true and try that out.


So I don't get my question. Is there any table like control in which a user can input the data?
Yes, SpreadSheet 11.0/10.0. I am still not sure why you do not want to use that control.


My table has fixed number of columns and the spreadsheet doesn't look suitable for it.
You can hide the columns in the Spreadsheet control. Just right-click the control and select "Commands and Options...". Then select the "Sheet" tab and set the "Viewable Range" to be only the columns you want, i.e. "$A:$G" or something like this. And it's much easier to work with in code. It has many members of the Excel.Application object.

I would HIGHLY recommend the Spreadsheet control, it has the most functionality and it would make users feel like they're working with excel...which to me seems more "User friendly/appealing" than the MsFlexGrid or DataGrid controls.

malik641
01-16-2007, 07:40 AM
Also, with the Spreadsheet control, you can set up the spreadsheet in design mode (when the userform is not "running"). You can include your headers, format cells, etc. The user can perform sorting, use the AutoFilter, and it has it's own help, and it's quite big for a userform control (what more can you ask for?). When the user is finished, the Spreadsheet control has its own button to "Export to Excel..." and when pressed, will create a new workbook and place the data inside. All without code, it's built-in.

maryam
01-16-2007, 09:10 AM
Dear Malik,
thank you for your complete reply. Yes I meant directly by typing. I will try DataGrid and Spreedsheet again.



100 times thanks,
Maryam

malik641
01-16-2007, 09:58 AM
No problem, and good luck Maryam :)

Please let us know how it turns out :thumb

-Joseph

maryam
01-17-2007, 04:46 AM
Dear Malik,
For DataGrid there is a seeting named "AllowAddnew", but when i set it to "True" still I cannot enter any data. I want my table to look like the one which is in the file the administrator attached to this post that's why I like to use DataGrid.



Regards,
Maryam

malik641
01-17-2007, 09:49 AM
Maryam,

I'm sorry, but the DataGrid control is a bit out of my league. I would like to look into it more, but it might take me some time. I'll see if I can do some reading on it over the weekend. At a glance, there's not much with the DataGrid using VBA, but plenty using VB.NET, ASP.NET, and ADO.NET. Apparently it is the only Microsoft Grid control in VB.NET.

I'll try to get back to this over the weekend.

Tommy
01-17-2007, 12:34 PM
Hi maryam, :hi:

View this link, it shows how to create an ADO recordset and populate the datagrid. After the datagrid is populated you can data entry, revise, change, ... This control and the Flexgrid Control are designed to be used with a database. This is not for the faint hearted!! This example only shows how to "change" the recordset, it does not show you how to get the information back from the recodset so you can use it. :bug:

http://www.codeguru.com/vb/controls/vb_activex/activex/article.php/c3491/

I recomend that you use the spreadsheet control as malik641 has suggested earlier. :thumb The control can hide columns and rows so it "appears" like it only has eg 3 rows/columns.

The attached is work that I did using malik641's spreadsheet and the posted link for reference. This will fill the grid and update the spreadsheet.:devil2:

maryam
01-18-2007, 05:25 AM
Dear Tommy,
Thanks for your reply. With ADO Recordset, We make a table and then use it as Datasource for DataGrid. But how to get the data back?I can make an empty datasource but how to save the data, the user input into the DataGrid in an excel sheet?

In your attached example you actually didnt use spreedsheet control, but you put DataGrid and MSFlex bottons on an excel sheet. I want the dataGrid to be empty in the beginning and then after the user input the data I want to have all those data in a excel sheet as well. So this example is good for the second purpose, but how to make the DataGrid empty in the beginning? Please look at the attached file to post #22, There u see a table at the right side. [We have a list box in which the components are clear( The user selected these components from an option list in advance and then the selected components are shown in a list box). ]The first column of this table is comming from the list box. So the number of raws will be the same as the number of selected components. The cells under the column with header "value", are empty and the user can input the values there. I hope I could explain clearly. Looking forward to your post.




Best Regards,
Maryam

maryam
01-18-2007, 06:13 AM
Dear Tommy,
In the attached example why the columns headers in the DataGrid are not the one written in the excel sheet:think:

malik641
01-18-2007, 07:12 AM
Maryam,

If I could still persuade you on using the spreadsheet control, check out this example. It has no code in it, and you can export it out to excel. The beauty of it is that when you export it, it comes with the formatting that's in the spreadsheet control. So you need no code to do the formatting. Which is VERY handy. Formatting an excel spreadsheet by code is cumbersome and annoying.

You can connect to a DataSource with the spreadsheet control, or you can import XLM, CSV, and HTML data from a URL. Check out "Commands and Options" when you right-click the spreadsheet control while in design mode (or click the toolbar button) and play around. You can even remove the tool bar if you want, along with the Column Letters and Row numbers. Making it look similar to the DataGrid, but it's more flexible.

Please take a look.

Thanks,
Joseph

Tommy
01-18-2007, 07:59 AM
In the attached example why the columns headers in the DataGrid are not the one written in the excel sheet
I flip-flopped the row,column I have column, row in the UserForm_Initialize. :rotlaugh:

I showed the example of the datagrid because you seemed to want to use it instead of the speadsheet control. Please note: malik641 was the original person to post the xls file. He also has posted another file for the spreadsheet control.

But just because I'm started :) the below code will give a blank row for the user to edit. you will need to add a command button to the form and call it cmdAddNew if you want the user to be able to enter more lines of input. The attachment is the modified version. The spreadsheet will be updated on the termination of the form.

Option Explicit
Private r As ADOR.Recordset
Private iCols As Long, iRows As Long
Private Sub cmdAddNew_Click()
r.AddNew
iRows = iRows + 1
End Sub

Private Sub UserForm_Initialize()

Dim i As Long, j As Long, k As Long

Dim lCount As Long
Dim Rng As Range
iCols = ActiveSheet.UsedRange.Columns.Count
iCols = 2
iRows = ActiveSheet.UsedRange.Rows.Count
iRows = 2
'
' Create a new disconnected recordset object
'
Set r = New ADOR.Recordset
'
' Setup the fields - you can use any valid type of
' ado field type for these. I've used VarChar just
' for testing / demonstration purposes.
'
' Add the columns
r.Fields.Append "DataFromListBox", adVarChar, 50
r.Fields.Append "Value", adVarChar, 50
Set Rng = ActiveSheet.UsedRange
r.CursorType = adOpenDynamic
r.Open
For j = 2 To iRows 'skip header
r.AddNew
For k = 1 To iCols
r.Fields(k - 1).Value = ""
Next
Next
'
' Populate the datagrid
'
Set DataGrid1.DataSource = r
End Sub
Private Sub UserForm_Terminate()
Dim j As Long, k As Long
r.UpdateBatch
r.MoveFirst
For j = 1 To iRows - 1
For k = 1 To iCols
If j = 1 Then
ActiveSheet.Cells(j, k).Value = r.Fields(k - 1).Name
End If
ActiveSheet.Cells(j + 1, k).Value = r.Fields(k - 1).Value
Next
r.MoveNext
Next
Set r = Nothing
End Sub

maryam
01-19-2007, 06:02 AM
Thank you Malik and Tommy,
In my user form I have two list boxes and one Data Grid control.the user select component from listbox1 and selected components go to listbox2. The database(list of components) for listbox1 comes from an excel sheet (I defined Rowsource, Column E) and the selected components of listbox1 will first go to the excel sheet( ColumnB) and then will be loaded into listbox2(Roowsource for listbox2: !B4:B100). I would like to to vice versa for listbox2. I mean the selected data of the listbox1 first come to listbox2 and then to the excel sheet.
for dataGrid, I want the first column of data Grid to be the items of listbox2 and in the second column user input the values and these values go to the column in the excel sheet (column C infront of B) The number of the rows of the DataGrid should be the same as the number of the full rows of listbox2. I tried with listbox2.listcount but the listcount is not equal to the those rows which are filled and it (100-4).
Again I cannot attach my form to this post, I am afraid if I could explain well.



Regards,
maryam

maryam
01-21-2007, 11:33 PM
any help please?

maryam
01-22-2007, 05:39 AM
Private Sub CmdAdd_Click()
If ListBox1.ListIndex = -1 Then
Exit Sub
End If
' Checking that no duplicates of materials are present
For i = 0 To ListBox2.ListCount - 1
If ListBox1.Value = Worksheets("flowsheet").Range("B4").Offset(i, 0) Then
Beep
Exit Sub
End If
Next i

For i = 0 To 12


If Worksheets("flowsheet").Range("B4").Offset(i, 0) = "" Then
Worksheets("flowsheet").Range("B4").Offset(i, 0) = FrmMaterialStream.ListBox1.Value
Exit Sub
End If
Next i
End Sub


here the data from listbox1 go to excel sheet named "flowsheet" and then as I defined rowsource for listbox2, it will be replaced there as well.FOr DataGrid I used Tommy's codes but I want the rows of DataGrid to be the same as listbox2 items. but as I used a rowsource for listbox2 the listcount doeant work.

maryam
01-23-2007, 12:30 AM
Dear Tommy,
Did you look at my last posts? How can I input listbox2 lists to the first column of DataGrid automatically? Can I select the coumns in the active worksheet where I like toload the data of DataGrid? In your file is appears at the top left side of the active sheet.

maryam
01-23-2007, 12:38 AM
Dear Malik,
Did you read post #33 by Tommy? Did you try it? Can you help me with my new questions please?

JonPeltier
01-23-2007, 07:57 AM
I've only used the DataGrid control enough to determine that it was more trouble to me than I wanted to deal with for my applications. For most cases, I get by with a multi-column listbox and a set of textboxes and buttons to work with the selected row of the listbox. Here is a link to screen shots of two examples.

http://peltiertech.com/Excel/Commentary/MultiColumnListBox.html

Tommy
01-26-2007, 10:12 AM
Hi maryam,

I made up some data. "Sheet2" was the active sheet. On my form I have listbox1, listbox2, datagrid1, and cmdAdd as a command button. I populated listbox1 from a range (as you can see). Then populated listbox2 with the selected items from listbox1 on the listbox2 click and the datagrid1. then the user enters the values for the items selected. once finshed the user selects the command button to send the values to "Sheet3" this was where I put the data.

Option Explicit
Private r As ADOR.Recordset
Private InPutRange As ADOR.Recordset
Private iCols As Long, iRows As Long

Private Sub cmdAddNew_Click()
Dim mI As Long, k As Long
r.UpdateBatch
r.MoveFirst
For mI = 0 To r.RecordCount - 1
If mI = 0 Then
Worksheets("Sheet3").Cells(mI + 1, 1).Value = r.Fields(mI).Name
Worksheets("Sheet3").Cells(mI + 1, 2).Value = r.Fields(mI + 1).Name
Else
Worksheets("Sheet3").Cells(mI + 1, 1).Value = r.Fields(0).Value
Worksheets("Sheet3").Cells(mI + 1, 2).Value = r.Fields(1).Value
End If
If Not r.BOF Or Not r.EOF Then r.MoveNext
Next
Set r = Nothing
End Sub

Private Sub ListBox2_Click()
Dim mI As Long
For mI = 0 To ListBox1.ListCount
If ListBox1(mI).Selected Then
ListBox2.AddItem ListBox1(mI)
End If
Next
End Sub
Private Sub ListBox2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim mI As Long
For mI = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(mI) Then
ListBox2.AddItem ListBox1.List(mI)
r.AddNew
r.Fields(0).Value = ListBox1.List(mI)
End If
Next
Set DataGrid1.DataSource = r
End Sub
Private Sub UserForm_Initialize()

Dim i As Long, j As Long, k As Long
Dim Rng As Range
iCols = 1
iRows = 95
'
' Create a new disconnected recordset object
'
Set r = New ADOR.Recordset
Set InPutRange = New ADOR.Recordset
Set OutPutRange = New ADOR.Recordset
' Add the column
InPutRange.Fields.Append "DataFromExcelSheet", adVarChar, 50
r.Fields.Append "Selected", adVarChar, 50
r.Fields.Append "Values", adVarChar, 50
Set Rng = ActiveSheet.Range("H6:H100")
InPutRange.CursorType = adOpenDynamic
InPutRange.Open
For j = 1 To iRows
InPutRange.AddNew
InPutRange.Fields(0).Value = Rng(j).Text
ListBox1.AddItem Rng(j).Text
Next
r.CursorType = adOpenDynamic
r.Open
End Sub



Enjoy!:thumb

Tommy

maryam
01-27-2007, 08:02 PM
"listbox1 from a range (as you can see)", where can I see?
I cannot run, it shows me "Permission denied".

maryam
01-28-2007, 09:25 PM
Now its ok I can run Tommy's program.( I tought he defined a rowsource for listbox1).
The problme is that in sheet3 the number of rows is one less. I dont know how to fix it? I changed r.RecordCount - 1 to r.RecordCount but it gives debug. because Im=0 is just for the title, we should have r.RecordCount number of rows I think.

maryam
01-29-2007, 05:13 AM
To those who may read later:
Just do this change:
Worksheets("Sheet3").Cells(mI + 2, 1).Value = r.Fields(0).Value
Worksheets("Sheet3").Cells(mI + 2, 2).Value = r.Fields(1).Value

Thank u Tommy and thank you all.

Tommy
01-31-2007, 11:16 AM
This is where I was refering to "as you can see" for the range.
Set Rng = ActiveSheet.Range("H6:H100")

My Bad :mkay

Private Sub cmdAddNew_Click()
Dim mI As Long
r.UpdateBatch
r.MoveFirst
For mI = 0 To r.RecordCount - 1
If mI = 0 Then
Worksheets("Sheet3").Cells(mI + 1, 1).Value = r.Fields(0).Name
Worksheets("Sheet3").Cells(mI + 1, 2).Value = r.Fields(1).Name
Endif
Worksheets("Sheet3").Cells(mI + 2, 1).Value = r.Fields(0).Value
Worksheets("Sheet3").Cells(mI + 2, 2).Value = r.Fields(1).Value
If Not r.BOF Or Not r.EOF Then r.MoveNext
Next
Set r = Nothing
End Sub

malik641
02-02-2007, 06:04 PM
Maryam,

My apologies that I have been so late to return to this thread. I've been very busy lately (trust me, it's not likely I wouldn't be away for so long!). But I'm glad to see the hard part is over :)
I hope we (VBAX) can continue to help you with your questions in the future.

Tommy, thanks a lot for the help with this :thumb it's truly appreciated!

Tommy
02-02-2007, 08:42 PM
LOL I was away with no access to a PC for 5 whole days :) Otherwise I would have posted sooner.

Joseph you know I love a good challenge :) you know the ones where you go - what the???? ROFLMAO

johnske
02-03-2007, 01:27 AM
...Yes, SpreadSheet 11.0/10.0. I am still not sure why you do not want to use that control.


You can hide the columns in the Spreadsheet control. Just right-click the control and select "Commands and Options...". Then select the "Sheet" tab and set the "Viewable Range" to be only the columns you want, i.e. "$A:$G" or something like this. And it's much easier to work with in code. It has many members of the Excel.Application object.

I would HIGHLY recommend the Spreadsheet control, it has the most functionality and it would make users feel like they're working with excel...which to me seems more "User friendly/appealing" than the MsFlexGrid or DataGrid controls.Joseph,

This issue has been addressed in an earlier post by maryam regarding another OWC component (OWC chart). It appears maryam intends to eventually distribute this and therein lies the problem with OWC, here's the pertinent part of my reply to her in that thread...


OWC was only introduced in office 2000, so anyone using office 97 may have difficulties if they haven't installed OWC (it can be installed separately on '97).

Now I have office 2000 and my own examples that were created in office 2000 have been downloaded from here and from my own site over 80 times with not one post to say that it doesn't work.

However, I've noted that with others OWC files posted here that were created in office 2002 or 2003 - I can't get the OWC component to show simply because it was created in a later version.

In other words, the only real problem I see would be if someone were trying to view it in an earlier version than yours - but if you're using the earliest version (office 2000) the only problem then would be '97 users.As she's said there that she's using Office 2003 this severely restricts the use and is probably one of the reasons she's not even considering an OWC spreadsheet

malik641
02-03-2007, 06:46 AM
Joseph you know I love a good challenge :) you know the ones where you go - what the???? ROFLMAO LOL! That's how I felt with this one. And with the time restriction I had, this was definitely too difficult for me to handle in a timely manner.


johnske,
Thanks for clearing that up for me. I had no idea that OWC components had those types of issues. If I knew that before, I probably wouldn't have tried to convince Maryam so badly :doh:

maryam
02-05-2007, 02:05 AM
Dear All,
i read recent posts just now. I didn't get any email to look! Intresting posts between talented people.
I have a problem again. Tommy's form has two listboxes and a dataGrid, I have two forms the 1st form has listbox1 and listbox2 and the second one has DataGrid. Tommy defined Private r As ADOR.Recordset in General. So If I write set DataGrid1.DataSource=r in form2 it should also work, but it doesnt! Can u help me pls?

Tommy
02-05-2007, 07:54 AM
I modified the forms as requested.

Carl A
02-05-2007, 10:29 AM
Here is a example of editing directly in a Flex Grid control
http://vb-helper.com/howto_vba_editable_grid.html

maryam
02-06-2007, 09:10 PM
I dont want to have a
command button in userform2 to open Userform1. If I dont put showdataGrid botton, then when i open userform1 datagrid have one row only.

Tommy
02-07-2007, 07:31 AM
' This is the part that was added to the userform1
Private Sub UserForm_Initialize()
Set DataGrid1.DataSource = r
End Sub


Here is the change :)