Button to set colour of selected cells & then force sheet calculat

Having read up on two fantastically informative websites about excel colour
functions:
http://www.xldynamic.com/source/xld.ColourCounter.html
http://www.cpearson.com/excel/colors.htm

I have inserted the UDFs suggested so that the spreadsheet counts how many
red cells there are within a certain range. However, as explained on the
sites, changing a cell's colour does not trigger a worksheet calculation, so
if one or more cells change the result of the formula used is incorrect until
a manual sheet recalculation.

The xldynamic website suggests "to overcome this, you need to force a sheet
calculate. What I do in applications that use this technique is to create a
button(s) to set the colour(s), and within the code attached to the
button(s), I do a manual sheet calculate".

Unfortunately I do not know how to do this. Ideally I need to create a
button that will (a) turn the selected cells red and (b) then automatically
run a sheet calculation, so that whenever cells are altered with this button,
the formula is ALWAYS correct.

Could there also be a button included to turn all cells in the same range
back to "no fill" (i.e. clear answers so the user can start from scratch
again)?

Could anybody help this macro learner please?

Many thanks in advance, Neil Goldwasser

PS - I have only just this afternoon learned what a UDF is, and discovered
that I could only get it to work by placing it in a module, rather than
attaching it to a sheet's "Microsoft Excel Object". Is there a rule on when
to include new modules? Or can I put all the UDF's into one module?

Hi Neil,

Try something like:

'=============>>
Private Sub CommandButton1_Click()
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Please use the mouse to " _
& "select cells to be coloured", _
Title:="Colour Selector", _
Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then
rng.Interior.ColorIndex = 3
ActiveSheet.Calculate
End If
End Sub

'<<=============


> PS - I have only just this afternoon learned what a UDF is, and discovered
> that I could only get it to work by placing it in a module, rather than
> attaching it to a sheet's "Microsoft Excel Object". Is there a rule on
> when
> to include new modules? Or can I put all the UDF's into one module?


I would suggest that you always store UDF's in a standard module. Whether
you use more than one module is a matter of taste and organisation.


---
Regards,
Norman


"Neil Goldwasser" <[email protected]> wrote in
message news:[email protected]...
> Having read up on two fantastically informative websites about excel
> colour
> functions:
> http://www.xldynamic.com/source/xld.ColourCounter.html
> http://www.cpearson.com/excel/colors.htm
>
> I have inserted the UDFs suggested so that the spreadsheet counts how many
> red cells there are within a certain range. However, as explained on the
> sites, changing a cell's colour does not trigger a worksheet calculation,
> so
> if one or more cells change the result of the formula used is incorrect
> until
> a manual sheet recalculation.
>
> The xldynamic website suggests "to overcome this, you need to force a
> sheet
> calculate. What I do in applications that use this technique is to create
> a
> button(s) to set the colour(s), and within the code attached to the
> button(s), I do a manual sheet calculate".
>
> Unfortunately I do not know how to do this. Ideally I need to create a
> button that will (a) turn the selected cells red and (b) then
> automatically
> run a sheet calculation, so that whenever cells are altered with this
> button,
> the formula is ALWAYS correct.
>
> Could there also be a button included to turn all cells in the same range
> back to "no fill" (i.e. clear answers so the user can start from scratch
> again)?
>
> Could anybody help this macro learner please?
>
> Many thanks in advance, Neil Goldwasser
>
> PS - I have only just this afternoon learned what a UDF is, and discovered
> that I could only get it to work by placing it in a module, rather than
> attaching it to a sheet's "Microsoft Excel Object". Is there a rule on
> when
> to include new modules? Or can I put all the UDF's into one module?

Hi Neil,

If, alternatively, it was your intention that the user select the cells to
colour and press the button, without a selection input box, then try
instead:

'=============>>
Private Sub CommandButton2_Click()
Selection.Interior.ColorIndex = 3
Me.Calculate
End Sub
'<<=============


---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> Hi Neil,
>
> Try something like:
>
> '=============>>
> Private Sub CommandButton1_Click()
> Dim rng As Range
> On Error Resume Next
> Set rng = Application.InputBox("Please use the mouse to " _
> & "select cells to be coloured", _
> Title:="Colour Selector", _
> Type:=8)
> On Error GoTo 0
> If Not rng Is Nothing Then
> rng.Interior.ColorIndex = 3
> ActiveSheet.Calculate
> End If
> End Sub
>
> '<<=============
>
>
>> PS - I have only just this afternoon learned what a UDF is, and
>> discovered
>> that I could only get it to work by placing it in a module, rather than
>> attaching it to a sheet's "Microsoft Excel Object". Is there a rule on
>> when
>> to include new modules? Or can I put all the UDF's into one module?
>
>
> I would suggest that you always store UDF's in a standard module. Whether
> you use more than one module is a matter of taste and organisation.
>
>
> ---
> Regards,
> Norman
>
>
> "Neil Goldwasser" <[email protected]> wrote in
> message news:[email protected]...
>> Having read up on two fantastically informative websites about excel
>> colour
>> functions:
>> http://www.xldynamic.com/source/xld.ColourCounter.html
>> http://www.cpearson.com/excel/colors.htm
>>
>> I have inserted the UDFs suggested so that the spreadsheet counts how
>> many
>> red cells there are within a certain range. However, as explained on the
>> sites, changing a cell's colour does not trigger a worksheet calculation,
>> so
>> if one or more cells change the result of the formula used is incorrect
>> until
>> a manual sheet recalculation.
>>
>> The xldynamic website suggests "to overcome this, you need to force a
>> sheet
>> calculate. What I do in applications that use this technique is to create
>> a
>> button(s) to set the colour(s), and within the code attached to the
>> button(s), I do a manual sheet calculate".
>>
>> Unfortunately I do not know how to do this. Ideally I need to create a
>> button that will (a) turn the selected cells red and (b) then
>> automatically
>> run a sheet calculation, so that whenever cells are altered with this
>> button,
>> the formula is ALWAYS correct.
>>
>> Could there also be a button included to turn all cells in the same range
>> back to "no fill" (i.e. clear answers so the user can start from scratch
>> again)?
>>
>> Could anybody help this macro learner please?
>>
>> Many thanks in advance, Neil Goldwasser
>>
>> PS - I have only just this afternoon learned what a UDF is, and
>> discovered
>> that I could only get it to work by placing it in a module, rather than
>> attaching it to a sheet's "Microsoft Excel Object". Is there a rule on
>> when
>> to include new modules? Or can I put all the UDF's into one module?
>
>

Many thanks Norman! I continue to learn from you!
Both great suggestions, the winner this time being the second one. Although
I quite like the message box popping up, it opens in the middle of the screen
over the top of some of the cells that would need highlighting. Although we
both know that it is easy to drag the box to one side, some of my students
are really not used to computers at all (some having never touched one before
this term!) and it could confuse them. The second model avoids this potential
issue.

I've put the second one in and it works a treat!

Just out of curiosity, is there a way of specifying that a message box opens
anywhere other than the centre of the screen, or is it a big procedure to
alter this?

I also just had a few novice macro questions (sorry if they appear really
daft!). At first I couldn't run the macro or assign it to a button, until I
changed the "Private Sub" in the first line to just "Sub". In layman's terms,
what do these mean and what is the difference?

I was also just wondering what the differences are between inserting a macro
in the sheet under "Microsoft Excel Objects" and inserting a module and
putting it there?

Don't worry if these will take a long time to explain, but I really
appreciate you furthering my education.

Thanks again, Neil
-----------------------------------------------------------------------------------------------


"Norman Jones" wrote:

> Hi Neil,
>
> If, alternatively, it was your intention that the user select the cells to
> colour and press the button, without a selection input box, then try
> instead:
>
> '=============>>
> Private Sub CommandButton2_Click()
> Selection.Interior.ColorIndex = 3
> Me.Calculate
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
>
> "Norman Jones" <[email protected]> wrote in message
> news:%[email protected]...
> > Hi Neil,
> >
> > Try something like:
> >
> > '=============>>
> > Private Sub CommandButton1_Click()
> > Dim rng As Range
> > On Error Resume Next
> > Set rng = Application.InputBox("Please use the mouse to " _
> > & "select cells to be coloured", _
> > Title:="Colour Selector", _
> > Type:=8)
> > On Error GoTo 0
> > If Not rng Is Nothing Then
> > rng.Interior.ColorIndex = 3
> > ActiveSheet.Calculate
> > End If
> > End Sub
> >
> > '<<=============
> >
> >
> >> PS - I have only just this afternoon learned what a UDF is, and
> >> discovered
> >> that I could only get it to work by placing it in a module, rather than
> >> attaching it to a sheet's "Microsoft Excel Object". Is there a rule on
> >> when
> >> to include new modules? Or can I put all the UDF's into one module?
> >
> >
> > I would suggest that you always store UDF's in a standard module. Whether
> > you use more than one module is a matter of taste and organisation.
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> > "Neil Goldwasser" <[email protected]> wrote in
> > message news:[email protected]...
> >> Having read up on two fantastically informative websites about excel
> >> colour
> >> functions:
> >> http://www.xldynamic.com/source/xld.ColourCounter.html
> >> http://www.cpearson.com/excel/colors.htm
> >>
> >> I have inserted the UDFs suggested so that the spreadsheet counts how
> >> many
> >> red cells there are within a certain range. However, as explained on the
> >> sites, changing a cell's colour does not trigger a worksheet calculation,
> >> so
> >> if one or more cells change the result of the formula used is incorrect
> >> until
> >> a manual sheet recalculation.
> >>
> >> The xldynamic website suggests "to overcome this, you need to force a
> >> sheet
> >> calculate. What I do in applications that use this technique is to create
> >> a
> >> button(s) to set the colour(s), and within the code attached to the
> >> button(s), I do a manual sheet calculate".
> >>
> >> Unfortunately I do not know how to do this. Ideally I need to create a
> >> button that will (a) turn the selected cells red and (b) then
> >> automatically
> >> run a sheet calculation, so that whenever cells are altered with this
> >> button,
> >> the formula is ALWAYS correct.
> >>
> >> Could there also be a button included to turn all cells in the same range
> >> back to "no fill" (i.e. clear answers so the user can start from scratch
> >> again)?
> >>
> >> Could anybody help this macro learner please?
> >>
> >> Many thanks in advance, Neil Goldwasser
> >>
> >> PS - I have only just this afternoon learned what a UDF is, and
> >> discovered
> >> that I could only get it to work by placing it in a module, rather than
> >> attaching it to a sheet's "Microsoft Excel Object". Is there a rule on
> >> when
> >> to include new modules? Or can I put all the UDF's into one module?
> >
> >
>
>
>

Hi Neil,

> Just out of curiosity, is there a way of specifying that a message box
> opens
> anywhere other than the centre of the screen, or is it a big procedure to
> alter this?

Try:

'=============>>
Public Sub Tester()

Application.InputBox Prompt:="Any better?", _
Title:="Position Demo", _
Left:=10, _
Top:=200
End Sub
'<<=============

> I was also just wondering what the differences are between inserting a
> macro
> in the sheet under "Microsoft Excel Objects" and inserting a module and
> putting it there?

See Chip Pearson's discussion of modules at:

http://www.cpearson.com/excel/codemods.htm


---
Regards,
Norman



"Neil Goldwasser" <[email protected]> wrote in
message news:[email protected]...
> Many thanks Norman! I continue to learn from you!
> Both great suggestions, the winner this time being the second one.
> Although
> I quite like the message box popping up, it opens in the middle of the
> screen
> over the top of some of the cells that would need highlighting. Although
> we
> both know that it is easy to drag the box to one side, some of my students
> are really not used to computers at all (some having never touched one
> before
> this term!) and it could confuse them. The second model avoids this
> potential
> issue.
>
> I've put the second one in and it works a treat!
>
> Just out of curiosity, is there a way of specifying that a message box
> opens
> anywhere other than the centre of the screen, or is it a big procedure to
> alter this?
>
> I also just had a few novice macro questions (sorry if they appear really
> daft!). At first I couldn't run the macro or assign it to a button, until
> I
> changed the "Private Sub" in the first line to just "Sub". In layman's
> terms,
> what do these mean and what is the difference?
>
> I was also just wondering what the differences are between inserting a
> macro
> in the sheet under "Microsoft Excel Objects" and inserting a module and
> putting it there?
>
> Don't worry if these will take a long time to explain, but I really
> appreciate you furthering my education.
>
> Thanks again, Neil
>

Hi Neil,

I realise that I inadvertently, partially, elided over:

> I also just had a few novice macro questions (sorry if they appear really
> daft!). At first I couldn't run the macro or assign it to a button, until
> I
> changed the "Private Sub" in the first line to just "Sub". In layman's
> terms,
> what do these mean and what is the difference?

I assumed that you would use buttons from the Control Toolbox. If, in design
mode, you double-click on the button, you will be taken to the sheet's code
module and an empty button click event procedure will be added, e.g.:

Private Sub CommandButton1_Click()

End Sub

For immediate purrposes, and over simplifying, the sheet module should be
used for event procedures relating to the sheet. Other (non-event) macros
should normally be stored in a standard module. However, read Chip Pearson,
as suggested, for an in depth discussion.


---
Regards,
Norman



"Neil Goldwasser" <[email protected]> wrote in
message news:[email protected]...
> Many thanks Norman! I continue to learn from you!
> Both great suggestions, the winner this time being the second one.
> Although
> I quite like the message box popping up, it opens in the middle of the
> screen
> over the top of some of the cells that would need highlighting. Although
> we
> both know that it is easy to drag the box to one side, some of my students
> are really not used to computers at all (some having never touched one
> before
> this term!) and it could confuse them. The second model avoids this
> potential
> issue.
>
> I've put the second one in and it works a treat!
>
> Just out of curiosity, is there a way of specifying that a message box
> opens
> anywhere other than the centre of the screen, or is it a big procedure to
> alter this?
>
> I also just had a few novice macro questions (sorry if they appear really
> daft!). At first I couldn't run the macro or assign it to a button, until
> I
> changed the "Private Sub" in the first line to just "Sub". In layman's
> terms,
> what do these mean and what is the difference?
>
> I was also just wondering what the differences are between inserting a
> macro
> in the sheet under "Microsoft Excel Objects" and inserting a module and
> putting it there?
>
> Don't worry if these will take a long time to explain, but I really
> appreciate you furthering my education.
>
> Thanks again, Neil
>

Thanks again Norman. I'm really starting to pick things up now, little by
little.

Thanks for the reply regarding the position of the message box too. Although
your other suggestion worked an absolute treat, it's great to find out about
these other functions - I'm sure I'll use every single one of the nuggets of
information at some point.

By the way, you may be pleased to know that I used a couple of function- &
macro-based excel activities in my lesson observation on Monday (5th). Not
only did my students love them, my observer was also raving about them too!
Passed with flying colours!

Cheers again, Neil




"Norman Jones" wrote:

> Hi Neil,
>
> I realise that I inadvertently, partially, elided over:
>
> > I also just had a few novice macro questions (sorry if they appear really
> > daft!). At first I couldn't run the macro or assign it to a button, until
> > I
> > changed the "Private Sub" in the first line to just "Sub". In layman's
> > terms,
> > what do these mean and what is the difference?
>
> I assumed that you would use buttons from the Control Toolbox. If, in design
> mode, you double-click on the button, you will be taken to the sheet's code
> module and an empty button click event procedure will be added, e.g.:
>
> Private Sub CommandButton1_Click()
>
> End Sub
>
> For immediate purrposes, and over simplifying, the sheet module should be
> used for event procedures relating to the sheet. Other (non-event) macros
> should normally be stored in a standard module. However, read Chip Pearson,
> as suggested, for an in depth discussion.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Neil Goldwasser" <[email protected]> wrote in
> message news:[email protected]...
> > Many thanks Norman! I continue to learn from you!
> > Both great suggestions, the winner this time being the second one.
> > Although
> > I quite like the message box popping up, it opens in the middle of the
> > screen
> > over the top of some of the cells that would need highlighting. Although
> > we
> > both know that it is easy to drag the box to one side, some of my students
> > are really not used to computers at all (some having never touched one
> > before
> > this term!) and it could confuse them. The second model avoids this
> > potential
> > issue.
> >
> > I've put the second one in and it works a treat!
> >
> > Just out of curiosity, is there a way of specifying that a message box
> > opens
> > anywhere other than the centre of the screen, or is it a big procedure to
> > alter this?
> >
> > I also just had a few novice macro questions (sorry if they appear really
> > daft!). At first I couldn't run the macro or assign it to a button, until
> > I
> > changed the "Private Sub" in the first line to just "Sub". In layman's
> > terms,
> > what do these mean and what is the difference?
> >
> > I was also just wondering what the differences are between inserting a
> > macro
> > in the sheet under "Microsoft Excel Objects" and inserting a module and
> > putting it there?
> >
> > Don't worry if these will take a long time to explain, but I really
> > appreciate you furthering my education.
> >
> > Thanks again, Neil
> >
>
>