in a normal module I have:

Public Const instrAutofit As String _
= "This will add a control which will autofit the columns" _
& "and rows of the activesheest used range "

Public Const instrAutoSum As String _
= "This will add a control to the menu that appears when you right" _
& "click a cell on a worksheet.It will have the same functionality" _
& "as the usual Autosum control that is usually situated on the toolbar"

In a Userform module I have:

Option Explicit

Private Sub ChkAutofit_Click()
Call TextBox1_Change(instrAutofit)
End Sub

Private Sub ChkAutoSum_Click()
Call TextBox1_Change(instrAutoSum)
End Sub

Private Sub TextBox1_Change(xxx As String)
TextBox1 = xxx
End Sub

Why doesn't this work??
Should I be using different events?

Jason

Jason,

The Textbox Change event does not have arguments, it is automatically
triggered by a change in the textbox. It seems you want to load it, so why
not just try

Private Sub ChkAutofit_Click()
TextBox1.Text = instrAutofit
End Sub

Private Sub ChkAutoSum_Click()
TextBox1.Text = instrAutoSum
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...
in a normal module I have:

Public Const instrAutofit As String _
= "This will add a control which will autofit the columns" _
& "and rows of the activesheest used range "

Public Const instrAutoSum As String _
= "This will add a control to the menu that appears when you
right" _
& "click a cell on a worksheet.It will have the same
functionality" _
& "as the usual Autosum control that is usually situated on the
toolbar"

In a Userform module I have:

Option Explicit

Private Sub ChkAutofit_Click()
Call TextBox1_Change(instrAutofit)
End Sub

Private Sub ChkAutoSum_Click()
Call TextBox1_Change(instrAutoSum)
End Sub

Private Sub TextBox1_Change(xxx As String)
TextBox1 = xxx
End Sub

Why doesn't this work??
Should I be using different events?

Jason

Cheers Bob,

I've got loads of check boxes and I think I'm gonna have to use:

Private Sub ChkAutofit_Click()
If ChkAutofit then
TextBox1.Text = instrAutofit
else
TextBox1.Text = ""
end if
End Sub

So with the 30 checkboxes I've got its gonna run into a fair bit of
code - I was hoping to use an argument to cut down on the code
Any ideas matey??

Jason.


"Bob Phillips" wrote in message ...
Jason,

The Textbox Change event does not have arguments, it is automatically
triggered by a change in the textbox. It seems you want to load it, so why
not just try

Private Sub ChkAutofit_Click()
TextBox1.Text = instrAutofit
End Sub

Private Sub ChkAutoSum_Click()
TextBox1.Text = instrAutoSum
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...
in a normal module I have:

Public Const instrAutofit As String _
= "This will add a control which will autofit the columns" _
& "and rows of the activesheest used range "

Public Const instrAutoSum As String _
= "This will add a control to the menu that appears when you
right" _
& "click a cell on a worksheet.It will have the same
functionality" _
& "as the usual Autosum control that is usually situated on the
toolbar"

In a Userform module I have:

Option Explicit

Private Sub ChkAutofit_Click()
Call TextBox1_Change(instrAutofit)
End Sub

Private Sub ChkAutoSum_Click()
Call TextBox1_Change(instrAutoSum)
End Sub

Private Sub TextBox1_Change(xxx As String)
TextBox1 = xxx
End Sub

Why doesn't this work??
Should I be using different events?

Jason

Jason,

I am not clear how the different checkboxes get set, and how you control
them. Can you post some code that shows what happens for say just 2
textboxes and I will see if I can assist?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...


Cheers Bob,

I've got loads of check boxes and I think I'm gonna have to use:

Private Sub ChkAutofit_Click()
If ChkAutofit then
TextBox1.Text = instrAutofit
else
TextBox1.Text = ""
end if
End Sub

So with the 30 checkboxes I've got its gonna run into a fair bit of
code - I was hoping to use an argument to cut down on the code
Any ideas matey??

Jason.

Bob,

There is just one textbox and about 30 checkboxes.The form should
initialise with an empty textbox - easy enough:

txtInstructions = ""

When a checkbox is checked a certain set of instructions (a public
constant called, for example - instrAutofit) should appear in the
textbox that relate to the most recently checked box.I've shortened
things abit an I think I'll be ok, and just have to repeat the below
code 30 times:

Private Sub CheckBox1_Click()
If CheckBox1 Then textbox1 = instrAutofit
End Sub

I was hoping to not have to repeat this for every checkbox - but it
isn't such a hardship (...I suppose)

Thanks Bob,
Jason


"Bob Phillips" wrote in message ...
Jason,

I am not clear how the different checkboxes get set, and how you control
them. Can you post some code that shows what happens for say just 2
textboxes and I will see if I can assist?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...


Cheers Bob,

I've got loads of check boxes and I think I'm gonna have to use:

Private Sub ChkAutofit_Click()
If ChkAutofit then
TextBox1.Text = instrAutofit
else
TextBox1.Text = ""
end if
End Sub

So with the 30 checkboxes I've got its gonna run into a fair bit of
code - I was hoping to use an argument to cut down on the code
Any ideas matey??

Jason.

Jason,

You could have a commandbutton with the following click code

Select Case True
Case Checkbox1.Value : TextBox1.Text = Value1
Case Checkbox2.Value : TextBox1.Text = Value2
etc.
End Se;ect

and no checkbox click code. It will reduce the code and make more readable.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...
Bob,

There is just one textbox and about 30 checkboxes.The form should
initialise with an empty textbox - easy enough:

txtInstructions = ""

When a checkbox is checked a certain set of instructions (a public
constant called, for example - instrAutofit) should appear in the
textbox that relate to the most recently checked box.I've shortened
things abit an I think I'll be ok, and just have to repeat the below
code 30 times:

Private Sub CheckBox1_Click()
If CheckBox1 Then textbox1 = instrAutofit
End Sub

I was hoping to not have to repeat this for every checkbox - but it
isn't such a hardship (...I suppose)

Thanks Bob,
Jason


"Bob Phillips" wrote in message
...
Jason,

I am not clear how the different checkboxes get set, and how you control
them. Can you post some code that shows what happens for say just 2
textboxes and I will see if I can assist?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...


Cheers Bob,

I've got loads of check boxes and I think I'm gonna have to use:

Private Sub ChkAutofit_Click()
If ChkAutofit then
TextBox1.Text = instrAutofit
else
TextBox1.Text = ""
end if
End Sub

So with the 30 checkboxes I've got its gonna run into a fair bit of
code - I was hoping to use an argument to cut down on the code
Any ideas matey??

Jason.

Cheers Bob,

I think you may have something here - just needs a little 'tweaking'
So I put a commandbutton called "help" on the userform so that the
user can fill the text box whenever necessary with instruction
relating to the checkbox.

This idea will work fine if only one of the 30 checkboxes is activated
at the moment when the user hits the commandbutton.If checkbox 2 is
checked and the user checks checkbox 5, and then hits the help button
then the instructions for checkbox 2 appear because this comes first
in the select case code!

Anyway of making the select case code know which of the buttons was
last set to true?

Jason

"Bob Phillips" wrote in message ...
Jason,

You could have a commandbutton with the following click code

Select Case True
Case Checkbox1.Value : TextBox1.Text = Value1
Case Checkbox2.Value : TextBox1.Text = Value2
etc.
End Se;ect

and no checkbox click code. It will reduce the code and make more readable.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...
Bob,

There is just one textbox and about 30 checkboxes.The form should
initialise with an empty textbox - easy enough:

txtInstructions = ""

When a checkbox is checked a certain set of instructions (a public
constant called, for example - instrAutofit) should appear in the
textbox that relate to the most recently checked box.I've shortened
things abit an I think I'll be ok, and just have to repeat the below
code 30 times:

Private Sub CheckBox1_Click()
If CheckBox1 Then textbox1 = instrAutofit
End Sub

I was hoping to not have to repeat this for every checkbox - but it
isn't such a hardship (...I suppose)

Thanks Bob,
Jason


"Bob Phillips" wrote in message
...
Jason,

I am not clear how the different checkboxes get set, and how you control
them. Can you post some code that shows what happens for say just 2
textboxes and I will see if I can assist?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...


Cheers Bob,

I've got loads of check boxes and I think I'm gonna have to use:

Private Sub ChkAutofit_Click()
If ChkAutofit then
TextBox1.Text = instrAutofit
else
TextBox1.Text = ""
end if
End Sub

So with the 30 checkboxes I've got its gonna run into a fair bit of
code - I was hoping to use an argument to cut down on the code
Any ideas matey??

Jason.

Jason,

There is but it would need code for each checkbox, something which kinda
defeats the whole object doesn't it. This is the sort of thing I mean

Dim iLastCheck As Long

Private Sub CheckBox1_Click()
iLastCheck = 1
End Sub

Private Sub CheckBox2_Click()
iLastCheck = 2
End Sub

Private Sub CheckBox3_Click()
iLastCheck = 3
End Sub

Private Sub CommandButton1_Click()
Select Case iLastCheck
Case 1: TextBox1.Text = Value1
Case 2: TextBox1.Text = Value2
End Select

End Sub


Why not just use option buttons which will only allow one to be set?


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...
Cheers Bob,

I think you may have something here - just needs a little 'tweaking'
So I put a commandbutton called "help" on the userform so that the
user can fill the text box whenever necessary with instruction
relating to the checkbox.

This idea will work fine if only one of the 30 checkboxes is activated
at the moment when the user hits the commandbutton.If checkbox 2 is
checked and the user checks checkbox 5, and then hits the help button
then the instructions for checkbox 2 appear because this comes first
in the select case code!

Anyway of making the select case code know which of the buttons was
last set to true?

Jason

"Bob Phillips" wrote in message
...
Jason,

You could have a commandbutton with the following click code

Select Case True
Case Checkbox1.Value : TextBox1.Text = Value1
Case Checkbox2.Value : TextBox1.Text = Value2
etc.
End Se;ect

and no checkbox click code. It will reduce the code and make more
readable.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...
Bob,

There is just one textbox and about 30 checkboxes.The form should
initialise with an empty textbox - easy enough:

txtInstructions = ""

When a checkbox is checked a certain set of instructions (a public
constant called, for example - instrAutofit) should appear in the
textbox that relate to the most recently checked box.I've shortened
things abit an I think I'll be ok, and just have to repeat the below
code 30 times:

Private Sub CheckBox1_Click()
If CheckBox1 Then textbox1 = instrAutofit
End Sub

I was hoping to not have to repeat this for every checkbox - but it
isn't such a hardship (...I suppose)

Thanks Bob,
Jason


"Bob Phillips" wrote in message
...
Jason,

I am not clear how the different checkboxes get set, and how you
control
them. Can you post some code that shows what happens for say just 2
textboxes and I will see if I can assist?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...


Cheers Bob,

I've got loads of check boxes and I think I'm gonna have to use:

Private Sub ChkAutofit_Click()
If ChkAutofit then
TextBox1.Text = instrAutofit
else
TextBox1.Text = ""
end if
End Sub

So with the 30 checkboxes I've got its gonna run into a fair bit of
code - I was hoping to use an argument to cut down on the code
Any ideas matey??

Jason.

That's great Bob - I'll use that.
Each of the checkboxes represents a modification to Excel - so a user
may want to enable all the modifications at once (option buttons
aren't an option)

Thanks for the help

Jason

p.s what's "tiscali" - I see other people with the same address?


"Bob Phillips" wrote in message ...
Jason,

There is but it would need code for each checkbox, something which kinda
defeats the whole object doesn't it. This is the sort of thing I mean

Dim iLastCheck As Long

Private Sub CheckBox1_Click()
iLastCheck = 1
End Sub

Private Sub CheckBox2_Click()
iLastCheck = 2
End Sub

Private Sub CheckBox3_Click()
iLastCheck = 3
End Sub

Private Sub CommandButton1_Click()
Select Case iLastCheck
Case 1: TextBox1.Text = Value1
Case 2: TextBox1.Text = Value2
End Select

End Sub


Why not just use option buttons which will only allow one to be set?


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...
Cheers Bob,

I think you may have something here - just needs a little 'tweaking'
So I put a commandbutton called "help" on the userform so that the
user can fill the text box whenever necessary with instruction
relating to the checkbox.

This idea will work fine if only one of the 30 checkboxes is activated
at the moment when the user hits the commandbutton.If checkbox 2 is
checked and the user checks checkbox 5, and then hits the help button
then the instructions for checkbox 2 appear because this comes first
in the select case code!

Anyway of making the select case code know which of the buttons was
last set to true?

Jason

"Bob Phillips" wrote in message
...
Jason,

You could have a commandbutton with the following click code

Select Case True
Case Checkbox1.Value : TextBox1.Text = Value1
Case Checkbox2.Value : TextBox1.Text = Value2
etc.
End Se;ect

and no checkbox click code. It will reduce the code and make more
readable.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...
Bob,

There is just one textbox and about 30 checkboxes.The form should
initialise with an empty textbox - easy enough:

txtInstructions = ""

When a checkbox is checked a certain set of instructions (a public
constant called, for example - instrAutofit) should appear in the
textbox that relate to the most recently checked box.I've shortened
things abit an I think I'll be ok, and just have to repeat the below
code 30 times:

Private Sub CheckBox1_Click()
If CheckBox1 Then textbox1 = instrAutofit
End Sub

I was hoping to not have to repeat this for every checkbox - but it
isn't such a hardship (...I suppose)

Thanks Bob,
Jason


"Bob Phillips" wrote in message
...
Jason,

I am not clear how the different checkboxes get set, and how you
control
them. Can you post some code that shows what happens for say just 2
textboxes and I will see if I can assist?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jason" wrote in message
om...


Cheers Bob,

I've got loads of check boxes and I think I'm gonna have to use:

Private Sub ChkAutofit_Click()
If ChkAutofit then
TextBox1.Text = instrAutofit
else
TextBox1.Text = ""
end if
End Sub

So with the 30 checkboxes I've got its gonna run into a fair bit of
code - I was hoping to use an argument to cut down on the code
Any ideas matey??

Jason.