-
Feb 13th, 2014, 02:57 PM
#1
Thread Starter
Lively Member
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!
-
Feb 13th, 2014, 03:20 PM
#2
Re: Excel Macro: Allows User to Change Part of Cell Content (not all of the content)
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
-
Feb 15th, 2014, 09:14 PM
#3
Thread Starter
Lively Member
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?
-
Feb 15th, 2014, 09:23 PM
#4
Thread Starter
Lively Member
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
-
Feb 15th, 2014, 09:26 PM
#5
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
-
Feb 15th, 2014, 09:50 PM
#6
Thread Starter
Lively Member
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.
-
Feb 15th, 2014, 11:50 PM
#7
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
-
Feb 16th, 2014, 01:51 AM
#8
Thread Starter
Lively Member
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.
-
Feb 16th, 2014, 02:39 AM
#9
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|