Results 1 to 9 of 9

Thread: Excel Macro: Allows User to Change Part of Cell Content (not all of the content)

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Excel Macro: Allows User to Change Part of Cell Content (not all of the content)

    For Background, I have a cell that reads: "The Prescription Model for Exterior Applications etc...." when I go to alter the character in the cell (to delete or add content) I can change the formatting of the text. I.e. I can change the font, size, color etc., without altering the other characters within that same cell.

    So, my question is, can we create a macro that will allow the user to change/alter a selection of text from within a cell BUT WHICH DOES NOT CHANGE/ALTER other information in that cell?

    Here's an example of what I'm talking about

    "The Prescription Model for Exterior Applications etc..." (I want to change/alter as follows

    "The Prescription Model for Exterior Applications etc..."

    is this possible? I've tried for days, but I cannot figure this one out. I've probably thought myself into a corner on this one. Please help.

    Thank you in advance!

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel Macro: Allows User to Change Part of Cell Content (not all of the content)

    is this possible?
    yes loop through all characters in cell and change their properties

    here is a sample
    Code:
    t = "Exterior Applications"
    With Range("b11")
        strt = InStr(.Value, t)
        For cr = strt To strt + Len(t)
            .Characters(cr, 1).Font.Size = 14
        Next
    End With
    where t is the text to change and B11 contains the value to change
    Last edited by westconn1; Feb 13th, 2014 at 03:32 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Re: Excel Macro: Allows User to Change Part of Cell Content (not all of the content)

    the problem is that I do not want the range to be static. I need to be able to select whatever text I decide to alter within that text, change it, and then move on to the next cell and decide which text I would like to select and alter it as well.

    Am I not seeing something here?

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Re: Excel Macro: Allows User to Change Part of Cell Content (not all of the content)

    westconn1,

    here is an example of my code. Much appreciated if you can help.

    '
    ' Redden Macro
    ' reddens selected text with cell
    '
    ' Keyboard Shortcut: Ctrl+Shift+Z
    '
    Range("D6").Select
    ActiveCell.FormulaR1C1 = _
    "The percentage of the planned work that was accomplished is:"
    With ActiveCell.Characters(Start:=1, Length:=22).Font
    .Name = "Arial"
    .FontStyle = "Bold Italic"
    .Size = 11
    .Color = -13421773
    End With
    With ActiveCell.Characters(Start:=23, Length:=12).Font
    .Name = "Arial"
    .FontStyle = "Bold Italic"
    .Size = 14
    .Color = -16776961
    End With
    With ActiveCell.Characters(Start:=35, Length:=26).Font
    .Name = "Arial"
    .FontStyle = "Bold Italic"
    .Size = 11
    .Color = -13421773
    End With
    End Sub

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel Macro: Allows User to Change Part of Cell Content (not all of the content)

    Am I not seeing something here?
    how would you input dynamic text to the procedure and also which cell? you could use an inputbox but by that point you could almost do it as easily manually

    edit: your last post which i just see now, is no more dynamic than the example i posted, i do not know how you determine the text or which part to change font or other property
    Last edited by westconn1; Feb 15th, 2014 at 09:31 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Re: Excel Macro: Allows User to Change Part of Cell Content (not all of the content)

    westconn1,

    here is (hopefully some better) code:

    Sub Redden()
    '
    ' Redden Macro
    ' reddens selected text with cell
    '
    ' Keyboard Shortcut: Ctrl+Shift+Z
    '
    Dim myselection As Range
    Set myselection = Selection
    Selection.Select
    With ActiveCell.Characters.Font
    .Name = "Arial"
    .FontStyle = "Bold Italic"
    .Size = 11
    .Color = -13421773
    End With
    With ActiveCell.Characters.Font
    .Name = "Arial"
    .FontStyle = "Bold Italic"
    .Size = 14
    .Color = -16776961
    End With
    End Sub

    the problem is that is "reddens" the entire cell's content. I want to redden choice text within the cell, but not all text.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel Macro: Allows User to Change Part of Cell Content (not all of the content)

    once you go into edit mode in excel, which includes selecting text within a cell, vba code will not run at all, can not even edit code within the editor

    i have found a method, but it uses a custom activex dll

    method select characters
    press ctrl C (copy) and enter or tab to exit edit mode

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Set myclip = CreateObject("clipbrd.clipboard")
    tmp = myclip.GetText
    pos = InStr(Target, tmp)
    If pos > 0 And Len(tmp) > 0 Then Target.Characters(pos, Len(tmp)).Font.Size = 16
        myclip.Clear
    Set myclip = Nothing
    End Sub
    note this is in the worksheet change event for the worksheet

    you can download and find information for the activex
    http://www.vbforums.com/showthread.p...or-vba-vbs-etc

    you could also use the clipboard APIs instead of the activex
    there could be issues with other cells changing in rare cases, try it and see
    does not work with numeric values, only text values
    if you put a beep after changing font, you will know that something may have been changed, at least for testing
    If pos > 0 And Len(tmp) > 0 Then Target.Characters(pos, Len(tmp)).Font.Size = 16: Beep
    Last edited by westconn1; Feb 15th, 2014 at 11:58 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Re: Excel Macro: Allows User to Change Part of Cell Content (not all of the content)

    westconn1,

    I'm not familiar with activex... your explanations make perfect sense to me; actually that is what I was suspecting was the issue.

    I did try to place that code of yours into my spreadsheet, however, I'm sure I'm doing something wrong. I highlighted the text within the cell I wanted to alter, but nothing happened when I pressed tab or enter, I tried several way to activate this thing, but it's above my current skill level.

    I really appreciate your help and patience. You're a true professional.

    If you have the time to break it down in laymen terms I'd be willing to read however long that may be. I'm still learning this thing.

    If you could include a line to change color as well so that I can see the difference from what you've already written that would be a great learning experience for me.

    Thanks.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel Macro: Allows User to Change Part of Cell Content (not all of the content)

    If you could include a line to change color as well
    Code:
    If pos > 0 And Len(tmp) > 0 Then 
       Target.Characters(pos, Len(tmp)).Font.Size = 16
       Target.Characters(pos, Len(tmp)).Font.color = vbred
    end if
    I did try to place that code of yours into my spreadsheet, however, I'm sure I'm doing something wrong.
    did you download and register the activex? all the instruction are in the link above
    there are instructions for registering for 64 bit and vista and later os in later posts in the thread
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width