Dynamic Controls (CheckBox) using VBA in Excel

[akkurup]

Hi all,
I am new to this VBA area.
I have a situation where in I need to create CheckBox Dynamically.
For Example.
If the Sql statement reterieves three row i should display thre
checkBox on the same line along with the results.

Can Some one helpme out with the same

 

[Bob Phillips]

Here is some code that creates forms toolbar checkboxes

With ActiveSheet
.CheckBoxes.Add(372.75, 46.5, 126, 63).Select
Selection.OnAction = "Macro1"
.CheckBoxes.Add(482.75, 46.5, 126, 63).Select
Selection.OnAction = "Macro1"
.CheckBoxes.Add(592.75, 46.5, 126, 63).Select
Selection.OnAction = "Macro1"
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[akkurup]

let me start with "Thanks"
here's what iam doing
I am connecting to a database and reteriving the data.

Select emp,Empname from emp;
When i display the data I thought that i will have checkboxes als
displayed along with each rows i displayed.

I am successful in displaying data, but iam iam having trouble with th
checkboxes

 

[Bob Phillips]

Can you post all your code as I am struggling to visualise it?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[akkurup]

Hi Bob,

Here's the Code
In Here Iam Show the data in Column B and C.
I want to Provide a Checkbox In Column "A" for each row
I am Using Oracle OO4O driver for connecting to the database and
reterieve the data.


Sub Reterieve_data()
Dim strSQL As String
Dim strResult As String
Dim OraDynaSet As Object
Dim i As Integer
strSQL = "select ename, empno from emp"
Set OraDynaSet = objDataBase.DBCreateDynaset(strSQL, 0&)
If OraDynaSet.RecordCount > 0 Then
'There were records retrieved
OraDynaSet.MoveFirst
For i = 1 To OraDynaSet.RecordCount
ActiveSheet.Cells(i, 2) = OraDynaSet.Fields(0).Value
ActiveSheet.Cells(i, 3) = OraDynaSet.Fields(1).Value
OraDynaSet.MoveNext
Next i
End If
End Sub

 

[Bob Phillips]

Where do you want the checkboxes? Assuming beyind A & B

Sub Reterieve_data()
Dim strSQL As String
Dim strResult As String
Dim OraDynaSet As Object
Dim i As Integer
strSQL = "select ename, empno from emp"
Set OraDynaSet = objDataBase.DBCreateDynaset(strSQL, 0&)
If OraDynaSet.RecordCount > 0 Then
'There were records retrieved
OraDynaSet.MoveFirst
For i = 1 To OraDynaSet.RecordCount
With ActiveSheet
.Cells(i, 2) = OraDynaSet.Fields(0).Value
.Cells(i, 3) = OraDynaSet.Fields(1).Value
.CheckBoxes.Add(Cells(i, "D").Left, Rows(i).Top, 126, 18).Select
Selection.Caption = "Y/N?"
Selection.OnAction = "Macro1"
.CheckBoxes.Add(Cells(i, "E").Left, Rows(i).Top, 126, 18).Select
Selection.Caption = "Y/N?"
Selection.OnAction = "Macro1"
End With
OraDynaSet.MoveNext
Next i
End If
End Sub






--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[akkurup]

Hi Bob,
Thanks !!!!!!!!!!

That was wonderful. I just tried you tip yes it works.

Just Wondering!!!!

Regards & Thanks

Anil