![]() |
#1
|
||||
|
||||
![]()
Hi all. I would like a macro to indent by 10 the text in Column A upon typing ">". I found lots of vba on indents in my search but none came out that exactly matches what I am looking for.
Thank you. |
#2
|
||||
|
||||
![]()
Do you want the > to remain in the cell?
Only to do this if it's the first character in an empty cell, or typed at the beginning of am already populated cell? Details, details. |
#3
|
|||
|
|||
![]()
Sorry a few questions - Is there anything allready in column A? How Many Rows? Where would you type the >?
|
#4
|
|||
|
|||
![]()
Snap! I typed the posted, then forgot to submit. Great minds....
|
#5
|
||||
|
||||
![]()
Details...
The ">" is typed in Column A and it is the beginning of a string. Col A is part of a table. Sample attached that I should have done earlier. Thanks. |
#6
|
|||
|
|||
![]()
How about the below, in the worksheet you want it to work on (also example uploaded)
Code:
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Column = 1 Then If Left(Target.Value, 1) = ">" Then Target.InsertIndent 10 End If End If End Sub |
#7
|
||||
|
||||
![]()
That's quick Purfleet. How do I adjust the indent to a fraction of an inch, say .15?
Edit: The indent is okay. I did not see the code in the regular module. Is there a way that the indent takes effect upon typing the ">"? |
#8
|
|||
|
|||
![]()
Not sure you can, looks like indent only works fgrom 0 to 15 in excel.
If you dont want 10, just change the number to what works best, might also be possible for different triggers to indent a different number |
#9
|
||||
|
||||
![]()
Some issues in the code:
1. I need to run the macro in order to make the indent code take effect which is a longer process than just clicking the indent tool in the ribbon. 2. Even without the ">" at the beginning of a string, the macro makes an indent when run. |
#10
|
|||
|
|||
![]()
you shouldnt be running anything, if you put the code in the Worksheet (Pink) rather than a normal module it should trigger when the sheet changes.
You need to make sure the worksheet is selected in the left drop down (yelllow) and change on the right (green) I dont think the attached will cover everything you need - for example an undo or limiting the indent to only 10 for a > (what happens if you type >>?) |
#11
|
||||
|
||||
![]()
Ah okay. There are codes in the Module and Workbook in the first file that I copied. I deleted them and now it is perfect. I changed the InsertIndent 10 to 1. Thank you Purfleet.
Another please. When the tables are loaded to a power query, the indents are deleted in the process. What I do to return the indents in the resulting query table is by Find ">", Replace "five spaces >". Can the code above be changed to find the ">" then apply the indents? |
#12
|
|||
|
|||
![]()
I would run an update macro if you have existing data to indent, just the range then run (in a normal module). Obviously this can be updated/improved
Code:
Sub IndentArrows() Dim r As Range Dim c As Range Set r = Selection For Each c In r If Left(c.Value, 1) = ">" And c.IndentLevel = 0 Then c.InsertIndent 1 Next c End Sub |
#13
|
||||
|
||||
![]()
Again, thank you Purfleet. You have saved us from inconsistent indenting of data. Some users forget to indent and others use spaces, some make use of the asterisk and hyphen, making consolidation of tables very tedious.
|
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Change the Column reference 'S' to a range ??? Sheets("Customer").Range("S" & ActiveCell.Row) = Date | trevorc | Excel Programming | 1 | 03-04-2021 02:39 PM |
![]() |
ravl13 | Excel | 3 | 08-22-2017 12:26 PM |
Need help with conditional formatting & returning "Pass" / "Fail Results" in a Column N | mikey386 | Excel | 2 | 12-11-2014 01:14 PM |
the character "v" when typed acts like ctrl-v. | jim redfield | Word | 1 | 09-22-2012 05:19 AM |
Why the "decrease indent" doesn't affect the selected text to move???? | Jamal NUMAN | Word | 0 | 07-12-2011 04:10 PM |