Solved: Data Entry Control

[Tommy]

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

[/VBA]

Enjoy!

Tommy

[maryam]

"listbox1 from a range (as you can see)", where can I see?
I cannot run, it shows me "Permission denied".

[maryam]

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]

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]

This is where I was refering to "as you can see" for the range.
[vba] Set Rng = ActiveSheet.Range("H6:H100")
[/vba]
My Bad

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

[malik641]

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 it's truly appreciated!

[Tommy]

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]

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

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

[maryam]

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]

I modified the forms as requested.

[Carl A]

Here is a example of editing directly in a Flex Grid control
http://vb-helper.com/howto_vba_editable_grid.html

[maryam]

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]

[vba]
' This is the part that was added to the userform1
Private Sub UserForm_Initialize()
Set DataGrid1.DataSource = r
End Sub
[/vba]

Here is the change