Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-12-05
3
Medium Priority
?
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 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