Solved: Data Entry Control

[malik641]

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

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

[mdmackillop]

Here's Maryam's image.

[maryam]

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]

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]

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]

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]

No problem, and good luck Maryam

Please let us know how it turns out

-Joseph

[maryam]

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]

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]

Hi maryam,

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.

http://www.codeguru.com/vb/controls/...cle.php/c3491/

I recomend that you use the spreadsheet control as malik641 has suggested earlier. 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.

[maryam]

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]

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

[malik641]

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]

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.

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]

[maryam]

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]

any help please?

[maryam]

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

[maryam]

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]

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

[JonPeltier]

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