MsFlex or dbGrid

[Syed Haider Ali]

hi,

is there any solution to use msFlex or dbGrid within excel userForm.

Syed Haider Ali

[Shasur]

Ali

You can use the Flex Grid Control in Excel UserForm

Include the MSHFlexGrid from Tools-->Addirional Controls in VBE

--
http://vbadud.blogspot.com


"Syed Haider Ali" wrote:

>
> hi,
>
> is there any solution to use msFlex or dbGrid within excel userForm.
>
>
> Syed Haider Ali
>
>
> --
> Syed Haider Ali
> ------------------------------------------------------------------------
> Syed Haider Ali's Profile: http://www.excelforum.com/member.php...o&userid=21994
> View this thread: http://www.excelforum.com/showthread...hreadid=564062
>
>

[keepITcool]

It's a while ago.. but I remember that when I used a flexgrid via the
Designer I had severe problems with file corruption.
My solution was to create the control "on-the-fly".

The example create and fills a flexgrid
(make sure you select a non-empty area in your sheet when
you fire up the form


Following code is also latebound.
NOT necessary but you could add a reference to
Microsoft FlexGrid Control 6.0 (SP3)
...(c:\windows\system32\msflxgrd.ocx)

Create a userform with 1 commandbutton at the top.
Name:cmdExec Caption:Populate

Copy following code:
Option Explicit

Dim flxGrid As Control

Function fLetter(ByVal i As Long) As String
Select Case i
Case 1 To 26
fLetter = Chr$(64 + i)
Case 27 To 702
fLetter = Chr$(64 + (i - 1) \ 26) & Chr$(65 + (i - 1) Mod _
26)
Case 703 To 16384 'office 12 proof
fLetter = Chr$(64 + (i - 1) \ 676)
i = 1 + ((i - 1) Mod 676)
fLetter = fLetter & Chr$(64 + (i - 1) \ 26) & Chr$(65 + _
(i - 1) Mod 26)
End Select
End Function

Sub Flex_Fill()
Dim rData As Range
Dim r&, c&

Set rData = ActiveCell.CurrentRegion

With flxGrid
.Clear
.Rows = 0
.Cols = 0

'Size the grid
.Rows = rData.Rows.Count + 1: .FixedRows = 1
.Cols = rData.Columns.Count + 1: .FixedCols = 1

'ColHeaders
For c = 1 To rData.Columns.Count
.TextMatrix(0, c) = fLetter(rData.Column + c - 1)
.FixedAlignment(c) = 4 ' flexAlignCenterCenter
Next

'RowHeaders
For r = 1 To rData.Rows.Count
.TextMatrix(r, 0) = rData.Row + r - 1
.ColWidth(0) = 500 'Twips!
Next

'Data
Dim v
v = rData.Value
For r = LBound(v, 1) To UBound(v, 1)
For c = LBound(v, 2) To UBound(v, 2)
If IsError(v(r, c)) Then
On Error Resume Next
v(r, c) = "Err:" & CStr(CLng(v(r, c)))
On Error GoTo 0
End If
.TextMatrix(r + .FixedRows - LBound(v, 1), c + _
.FixedCols - LBound(v, 2)) = v(r, c)
Next
Next

End With

End Sub

Sub Flex_Init()
With Me
On Error Resume Next
Set flxGrid = .Controls.Add("msflexgridlib.msflexgrid", _
"flxGrid", True)
If Err.Number Then
MsgBox "Cant create a flexgrid!", vbCritical
Exit Sub
End If
End With

With flxGrid
.Move 3, cmdExec.Top + cmdExec.Height + 3, Me.InsideWidth - _
6, Me.InsideHeight - cmdExec.Height - 6
.AllowBigSelection = False
.AllowUserResizing = 3 'flexResizeBoth
.Appearance = 1 'flex3D
.BorderStyle = 1 'flexBorderSingle
.BackColorBkg = vbApplicationWorkspace
.ScrollBars = 3 'flexScrollBarBoth
.WordWrap = True
End With
End Sub


Private Sub cmdExec_Click()
Flex_Fill
End Sub

Private Sub UserForm_Initialize()
Flex_Init
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Syed Haider Ali wrote in
<news:<Syed.Haider.Ali.2bdyh0_1153637103.98@excelforum-nospam.com>

>
> hi,
>
> is there any solution to use msFlex or dbGrid within excel userForm.
>
>
> Syed Haider Ali