Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-03-2021, 11:32 PM
Marcia's Avatar
Marcia Marcia is offline Indent when ">" is typed in Column A Windows 7 32bit Indent when ">" is typed in Column A Office 2013
Expert
Indent when ">" is typed in Column A
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Indent when ">" is typed in Column A

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.
Reply With Quote
  #2  
Old 04-04-2021, 06:00 AM
p45cal's Avatar
p45cal p45cal is offline Indent when ">" is typed in Column A Windows 10 Indent when ">" is typed in Column A Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Reply With Quote
  #3  
Old 04-04-2021, 07:44 AM
Purfleet Purfleet is offline Indent when ">" is typed in Column A Windows 10 Indent when ">" is typed in Column A Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Sorry a few questions - Is there anything allready in column A? How Many Rows? Where would you type the >?
Reply With Quote
  #4  
Old 04-04-2021, 07:45 AM
Purfleet Purfleet is offline Indent when ">" is typed in Column A Windows 10 Indent when ">" is typed in Column A Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Quote:
Originally Posted by p45cal View Post
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.
Snap! I typed the posted, then forgot to submit. Great minds....
Reply With Quote
  #5  
Old 04-04-2021, 02:37 PM
Marcia's Avatar
Marcia Marcia is offline Indent when ">" is typed in Column A Windows 7 32bit Indent when ">" is typed in Column A Office 2013
Expert
Indent when ">" is typed in Column A
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Attached Files
File Type: xlsx Indent.xlsx (9.7 KB, 7 views)
Reply With Quote
  #6  
Old 04-05-2021, 12:18 PM
Purfleet Purfleet is offline Indent when ">" is typed in Column A Windows 10 Indent when ">" is typed in Column A Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Attached Files
File Type: xlsm Indent_Purfleet.xlsm (16.5 KB, 6 views)
Reply With Quote
  #7  
Old 04-05-2021, 12:44 PM
Marcia's Avatar
Marcia Marcia is offline Indent when ">" is typed in Column A Windows 7 32bit Indent when ">" is typed in Column A Office 2013
Expert
Indent when ">" is typed in Column A
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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 ">"?
Reply With Quote
  #8  
Old 04-05-2021, 12:54 PM
Purfleet Purfleet is offline Indent when ">" is typed in Column A Windows 10 Indent when ">" is typed in Column A Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #9  
Old 04-05-2021, 06:08 PM
Marcia's Avatar
Marcia Marcia is offline Indent when ">" is typed in Column A Windows 7 32bit Indent when ">" is typed in Column A Office 2013
Expert
Indent when ">" is typed in Column A
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #10  
Old 04-05-2021, 08:49 PM
Purfleet Purfleet is offline Indent when ">" is typed in Column A Windows 10 Indent when ">" is typed in Column A Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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 >>?)
Attached Images
File Type: jpg indentp.jpg (56.5 KB, 18 views)
Attached Files
File Type: xlsm Indent_Purfleet2.xlsm (16.0 KB, 4 views)
Reply With Quote
  #11  
Old 04-05-2021, 09:41 PM
Marcia's Avatar
Marcia Marcia is offline Indent when ">" is typed in Column A Windows 7 32bit Indent when ">" is typed in Column A Office 2013
Expert
Indent when ">" is typed in Column A
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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?
Reply With Quote
  #12  
Old 04-06-2021, 12:08 AM
Purfleet Purfleet is offline Indent when ">" is typed in Column A Windows 10 Indent when ">" is typed in Column A Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Attached Files
File Type: xlsm Indent_Purfleet3.xlsm (18.0 KB, 5 views)
Reply With Quote
  #13  
Old 04-06-2021, 02:24 AM
Marcia's Avatar
Marcia Marcia is offline Indent when ">" is typed in Column A Windows 7 32bit Indent when ">" is typed in Column A Office 2013
Expert
Indent when ">" is typed in Column A
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
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
Indent when ">" is typed in Column A Formula to format "I" column based on if "F" column row is not blank 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:32 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft