Private Sub cmdMakeGrid_Click()
Dim frm As Access.Form
DoCmd.OpenForm "frmDefineGrid", , , , , acDialog
If CurrentProject.AllForms("frmDefineGrid").IsLoaded Then
Set frm = Forms("frmDefineGrid")
If IsNumeric(frm.txtRows) And IsNumeric(frm.txtColumns) Then
If frm.txtRows * frm.txtColumns <= 200 Then
formatGrid frm.txtRows, frm.txtColumns
Else
MsgBox "Max controls is 200"
End If
End If
DoCmd.Close acForm, frm.Name
End If
End Sub
Public Sub formatGrid(x As Long, y As Long)
Const ctlWidth = (0.75 * 1440)
Const ctlHeight = (0.25 * 1440)
Const conStartLeft = (0.5 * 1440)
Const conStartTop = (1 * 1440)
Dim startLeft As Long
Dim startTop As Long
Dim lngRow As Long
Dim lngCol As Long
Dim intCounter As Long
Dim ctl As Access.Control
startLeft = conStartLeft
startTop = conStartTop
For lngRow = 1 To y
For lngCol = 1 To x
intCounter = intCounter + 1
Set ctl = Me.Controls("lblGrid" & intCounter)
With ctl
.Height = ctlHeight
.Width = ctlWidth
.Left = startLeft
.Top = startTop
.Caption = "Row" & lngRow & " Col" & lngCol
.FontSize = 8
.Visible = True
.Tag = lngRow & ";" & lngCol
'Define the function here that they do when clicked.
.OnClick = "=gridClick()"
End With
startLeft = startLeft + ctlWidth
Next lngCol
startLeft = conStartLeft
startTop = startTop + ctl.Height
Next lngRow
End Sub
Public Sub makeAllInvisible()
Dim ctl As Access.Control
'need somewhere to set focus it is hidden below the command button
Me.txtFocus.SetFocus
For Each ctl In Me.Controls
If Left(ctl.Name, 7) = "lblGrid" Then
ctl.Visible = False
End If
Next ctl
End Sub
Public Sub stackControls()
Dim ctl As Access.Control
For Each ctl In Forms("frmGrid")
If Left(ctl.Name, 7) = "lblGrid" Then
ctl.Left = 0
ctl.Top = 0
ctl.Height = 0.2 * 1440
ctl.Width = 0.2 * 1440
End If
Next ctl
End Sub
Public Function gridClick()
'This function fires when any dynamic control clicks
Dim ctl As Access.Control
Dim lngRow As Long
Dim lngCol As Long
Set ctl = ActiveControl
lngRow = Split(ctl.Tag, ";")(0)
lngCol = Split(ctl.Tag, ";")(1)
MsgBox ctl.Name & "Row " & lngRow & " Column " & lngCol
If ctl.ForeColor = vbRed Then
ctl.ForeColor = vbBlack
Else
ctl.ForeColor = vbRed
End If
End Function
Private Sub Form_Open(Cancel As Integer)
Call makeAllInvisible
DoEvents
MsgBox "Click Button to create grid"
End Sub