Solved: Data Entry Control

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.

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.

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?

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...?

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

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.

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?

Hi Joseph, glad you dropped in on this one.

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

How can I attach a file? the attach icon is not working.

Dear Malik and lucas,
I cannot attach my file.Would you pls send me your your emails so that I can send it.

To attach a file, use Manage Attachments in the Go Advanced section.

Quote:

---

Originally Posted by lucas

Hi Joseph, glad you dropped in on this one.

---

:)


Maryam,

Quote:

---

Originally Posted by 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.

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

Hi Maryam,
Instead of clicking Post Quick Reply, click Go Advanced

Dear Administrator,
I just click on post reply, where is advance?

......

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

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.

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

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

Here's Maryam's image.

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:-(.

Quote:

---

Originally Posted by maryam

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.

Quote:

---

Originally Posted by maryam

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.

Quote:

---

Originally Posted by maryam

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.

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.

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

No problem, and good luck Maryam :)

Please let us know how it turns out :thumb

-Joseph

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

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.

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/...cle.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:

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

Dear Tommy,
In the attached example why the columns headers in the DataGrid are not the one written in the excel sheet:think:

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

Quote:

---

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.

[vba]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
[/vba]

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

any help please?

[VBA]
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
[/VBA]

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.

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.

Dear Malik,
Did you read post #33 by Tommy? Did you try it? Can you help me with my new questions please?

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/Comment...mnListBox.html