Solved

Changing Text in a Range Named and Macro Assigned "Box"

Posted on 2013-12-05
3
225 Views
Last Modified: 2013-12-05
EE Pros,

I have a graphic where I'm trying to change the text in the textbox in cell (B2) from "Select an Outcome" to "Select an Area of Optimization".  The problem I have run across is that the text is used in a macro and is also a range name and I cannot figure out how to change the text (I've tried to change the range name and tried to change the text in the Macro).

I'm looking for "how" to do this so I can learn.

Thank you in advance.

B.
Changing-Text-in-a-Range.xlsm
0
Comment
Question by:Bright01
3 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39698113
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim cel                    As Range
   Dim rInitSel               As Range
   Dim rSelections            As Range
   Dim lFreeCells             As Long

   ' input cell to monitor
   Set rInitSel = Range(mc_sINPUT_CELL)

   If Not Intersect(Target, rInitSel) Is Nothing Then

      ' don't act when no valid selection
      If rInitSel.Value = "Select an Outcome" Then Exit Sub

      ' this is the shaded range for the selected outcomes
      Set rSelections = Range(mc_sSELECTED_OUTCOMES)

      On Error Resume Next
      Application.EnableEvents = False

      ' check how many selected outcome cells are empty
      lFreeCells = Application.WorksheetFunction.CountBlank(rSelections)
      If lFreeCells = 0 Then
         ' no cells available - offer to clear
         If MsgBox(prompt:="All available outcome cells are full. Do you wish to clear and start again?", Buttons:=vbYesNo) = vbNo Then
            ' do nothing
         Else
            ' clear the current selections
            ResetPrioritiesAll
            lFreeCells = rSelections.Count
         End If
      End If

      If lFreeCells > 0 Then
         ' get first available output cell
         With rSelections
            Set cel = .Cells(.Count - lFreeCells + 1)
         End With
         cel.Value = rInitSel.Value
      End If
      ' reset B2 to standard text
      rInitSel.Value = "Select an Outcome"
      Application.EnableEvents = True
   End If


End Sub

Open in new window


Replace
      rInitSel.Value = "Select an outcome"

 with

  rInitSel.Value = "Select an Area of Optimization"

and replace

      If rInitSel.Value = "Select an Outcome" Then Exit Sub

with

      If rInitSel.Value = "Select an Area of Optimization" Then Exit Sub


Regards
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39698176
I cannot understand what you are looking for!
0
 

Author Closing Comment

by:Bright01
ID: 39698251
Raganzo,

Thank you!  Works now.  Appreciate you letting me know exactly what you did.  I had tried that earlier but had missed the second reference point.

I have another question outstanding; if you get a chance, take a look.

Thanks again,

B.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question