Excel Like Grid Control... 2

[CubeE101]

Are there any 'standard' VB controls you can set up to act like an Excel Grid without connecting to a data base?

I would rather not use a third party control... I just want to set up a 2 or 3 column list control...
And I assume I am going to need to use some type of Grid to do this...

Or is there a way to hide a Scroll bar on a ListBox?

Have Fun, Be Young... Code BASIC
-Josh

http://cubee.topcities.com

 

[SQLRickster]

You could actually embed Excel into a form using OLE. This will give you all of the power of Excel. This may not be what you want. Depends on what you need the grid to do. You could use a standard grid control and not bind the control to a data source. Getting data into the grid would require a text box or some means of adding data at run time. This may be more of what you need.

Hope this helps!
SQLRickster

 

[tudogs]

If you only want a multi column listbox, I would try MSFlexGrid.

hope this helps

Bob

 

[CCLINT]

You can use the DataGrid as well.

You would use a fabricated recordset (not connected to, or created off of a db table) and add the fields desired (column headers) and then open it and set the grid's datasource to this recordset. Again, this is NOT an external database dependent recordset.

The biggest draw back over using the MSFlexGrid is the abitlity to change color/font of individual columns, rows or cells (except the active cell, which can be changed to a different color/font). The positive side is that you can edit the cells directly and use the underlying recordset's methods and properties to navigate, sort, search, etc. with in code.


See thread222-626165 on how to create a fabricated recordset which shows how to create one and add data.

 

[CubeE101]

CCLINT,
That is what I originally tried to do, but I thought you had to have an existing external dB...

OK, to use...
Private m_RS As ADODB.Recordset
I need to set a reference to:
Microsoft ActiveX Data Objects 2.7 Library
Right?

After that is created, how do you connect the DataGrid to the m_RS, ADODB RecordSet?

Have Fun, Be Young... Code BASIC
-Josh

http://cubee.topcities.com

 

[CCLINT]

>OK, to use...
>Private m_RS As ADODB.Recordset

Yep.

>I need to set a reference to:
> Microsoft ActiveX Data Objects 2.7 Library
>Right?

Yep. Or > 2.1

>After that is created, how do you connect the DataGrid to the m_RS, ADODB RecordSet?

Remove the DataSource set in the properties window.
Then:
Set DataGrid1.DataSource = m_RS

 

[CubeE101]

Works so far...

But, now I'm getting an error when I change a record:
Multiple-step operation generated errors. Check each status value.

What does this mean???

Have Fun, Be Young... Code BASIC
-Josh

http://cubee.topcities.com

 

[CubeE101]

BTW...

Here is the code so far...

Option Explicit
Private m_RS As ADODB.Recordset

Private Sub CreateRS()
    Set m_RS = New ADODB.Recordset
    With m_RS
        .Fields.Append "Old", adDate, adFldUpdatable, adFldIsNullable Or adFldUpdatable
        .Fields.Append "New", adVarWChar, 50, adFldUpdatable Or adFldUpdatable
        .Open
    End With
End Sub

Private Sub Command1_Click()
  AddRSData
End Sub

Private Sub Form_Load()
  CreateRS
  Set DataGrid1.DataSource = m_RS
  AddRSData
End Sub

Private Sub AddRSData()
    With m_RS
        .AddNew 'add a record
        .Fields("Old") = #8/1/2003#
        .Fields("New") = "SomeString"
        .Update
    End With
End Sub

Have Fun, Be Young... Code BASIC
-Josh

http://cubee.topcities.com

 

[CubeE101]

OK...
The error is due to the Data Type of the field... it was set to Date...
.Fields.Append "Old", adDate, adFldUpdatable, adFldIsNullable Or adFldUpdatable

and I was trying to change it to '123'

Have Fun, Be Young... Code BASIC
-Josh

http://cubee.topcities.com

 

[CubeE101]

If you use MSFlexGrid, how do you set it up to click on a cell and modify the text?

Have Fun, Be Young... Code BASIC
-Josh

http://cubee.topcities.com

 

[tudogs]

You can move a textbox over the selected cell like so:

Private Sub movetextbox()
Text1.Left = MSF1.CellLeft + MSF1.Left
Text1.Top = MSF1.CellTop + MSF1.Top
Text1.Height = MSF1.CellHeight
Text1.Width = MSF1.CellWidth
Text1.Text = MSF1.Text
Text1.Visible = True
Text1.SetFocus
End Sub

In Text1.Keypress Event dump the text to the cell on Enter.


Good Luck

Bob

 

[CubeE101]

Or...

Dump the Text into the cell on LostFocus (or Validate)...
And trigger LostFocus from KeyPress when KeyAscii = 13 ;-)
Don't forget to set KeyAscii to 0 before exiting the KeyPress sub to avoid the beeps...

This is the code I was using to Change the ListBox's in the program...

Private Sub Text1_KeyPress(KeyAscii As Integer)
  If KeyAscii = 13 Then
    Text1_Validate False
    KeyAscii = 0
  End If
End Sub

Private Sub Text1_LostFocus()
  Text1.Visible = False
End Sub

Private Sub Text1_Validate(Cancel As Boolean)
  If Text1 = "" Then
    Cancel = True
  Else
    List1.List(List1.ListIndex) = Text1
    List1.SetFocus
  End If
End Sub

This way, if you click outside of the textbox or press enter the same code is applied, and it is easier to maintain ;-)
It will also not allow a Null value...


Have Fun, Be Young... Code BASIC
-Josh

http://cubee.topcities.com

PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.

 

[tudogs]

Yeah all that too.

Only had time to show skeleton.

Got left alone on the phones this morning.
The Bl***y things won't stay quite.

Looks like you have a handle on things.

Good Luck

Bob

 

[CubeE101]

Cool, It looks like I'm gonna go with the Flex Grid...
Thanks for the tip, tudogs... have a star ;-)

Here is the Code I am using to handle the Grid I/O...

Private Sub Command1_Click()
  MSFG1.AddItem "", MSFG1.Row + 1
End Sub

Private Sub Command2_Click()
  MSFG1.RemoveItem MSFG1.Row
End Sub

Private Sub MSFG1_DblClick()
  Text1.Move MSFG1.CellLeft + MSFG1.Left, _
             MSFG1.CellTop + MSFG1.Top - 25, _
             MSFG1.CellWidth
  Text1.Text = MSFG1.Text
  Text1.SelStart = 0
  Text1.SelLength = Len(Text1)
  Text1.Visible = True
  Text1.SetFocus
End Sub

Private Sub MSFG1_KeyPress(KeyAscii As Integer)
  Text1.Move MSFG1.CellLeft + MSFG1.Left, _
             MSFG1.CellTop + MSFG1.Top - 25, _
             MSFG1.CellWidth
  Text1.Text = Chr(KeyAscii)
  Text1.Visible = True
  Text1.SelStart = Len(Text1)
  Text1.SetFocus
End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)
  If KeyAscii = 13 Then
    Text1_Validate False
    KeyAscii = 0
  End If
End Sub

Private Sub Text1_LostFocus()
  Text1.Visible = False
End Sub

Private Sub Text1_Validate(Cancel As Boolean)
  MSFG1.Text = Text1
  MSFG1.SetFocus
End Sub

And...
Thank you to everyone who posted

Have Fun, Be Young... Code BASIC
-Josh

http://cubee.topcities.com

PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.

 

[CCLINT]

There is a FAQ on this (FlexGrid Edit control) with an example.

 

[tudogs]

CubeE:

Thanks for the *

Happy Computing

Bob