Here is a paste from a post I made a week or so back and
I still need help with... the latest reply having not
been answered and I still need the issue addressed!

////////////////////////////////////////////////////////

Hi!

Thanks for the help, much appreciated... theres one
problem though...

The textboxes I'm using are not on a form, they are "on
top" of the spreadsheet I have created, for want of a
better term. Values are then entered into the
textboxes... which were then originally passing the data,
through the linked cell, as a string. The code I am
putting in is to convert it to an integer for use in
calculations on the main sheet. The main jist of what I'm
saying... the textbox controls are not part of a form,
they are all just individually linked, through their
properties, to the spreadsheet.

A major problem I have found is if anyone adds / removes
rows it messes up the entire sheet as all the linked
cells for the textboxes apparently change... hence the
reason why I'm trying to just do it all through code, the
code for each textbox being the same and just passing the
changing variables (the linked cell and cell value)
through to the function to be converted.

I'm guessing some slight modifications to this solution
would create a working demo?

Thanks for any more help.

Bryn


-----Original Message-----
Here's one fro all you budding programmers!
The issue here is that the textboxes already exist. Its
straightforward enoght adding textboxes, but we need to
do something clever.

So. The trick is to iterate through all the controls,
adding those that are required to a collection, and also
using our friendly class to trap events.

So here's a demo, and please email direct for the file
if
you want.

There are three parts
a) create a userform
b) add the class
c) add some code to respond to the events.

so, we'll start with (c)
add a standard module and put this code into it:

Sub PastReply(msg As String, cbx As Control)
With UserForm1
.Label1.Caption = msg
Set .ctrl = cbx
End With
End Sub

this procedure takes a text string in the variable msg
and pops ito a label on the userform, plus passes the
contro itself back to the form

(b) add a class module
This will hold the ckeck box (for my example) and also
set the changed event to call the procedure that we just
wrote. Copy the following into the class module:

Option Explicit

Public WithEvents m_txtMyCheckBox As MSForms.CheckBox

Public Event Changed(text As String)


Private Sub m_txtMyCheckBox_Click()
PastReply "You clicked " & m_txtMyCheckBox.Caption _
& vbLf & _
m_txtMyCheckBox.Value, _
txtMyCheckBox

End Sub

(a) now create the form.
Add a new userform ( userform1) with a dozen or so
checkboxes, each should have a different caption. We
could do this in code, but our questioner already has
the
controls. add a label (label1) and a button (cmdClose)

Add this code to the userform:
Option Explicit
Dim colCheckBoxes As New Collection
Dim WithEvents ctCheckBox As clsControl
Public ctrl As Control

Private Sub UserForm_Initialize()
Set ctCheckBox = New clsControl
Dim ctrl As Control
For Each ctrl In Controls
If ctrl.Name Like "Check*" Then
Add_A_Control ctrl
End If
Next
End Sub


Private Sub Add_A_Control(ctrl As Control)


Set ctCheckBox = New clsControl
Set ctCheckBox.m_txtMyCheckBox = ctrl
colCheckBoxes.Add ctCheckBox

End Sub


Private Sub cmdClose_Click()
Unload Me
End Sub

The initialize iterates through the controls, adding the
checkboxes to the collection that hosts the classes. My
controls' names all start with the word"check" eh
checkbox1, checkbox2 etc

Now when you run the form, each time you click a
checkbox, the event is fired and the label shows you
which box you checked.

It will be a minor task to change to code for text
boxes....and as you can see, you'd only need to port a
tiny amout of code.

Patrick Molloy
Microsoft Excel MVP