+ Reply to Thread
Page 16 of 17 FirstFirst ... 6 14 15 16 17 LastLast
Results 226 to 240 of 243

How to post a range - headers and data?

  1. #226
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,426

    Re: How to post a range - headers and data?

    Hi Tony
    Quote Originally Posted by Tony Valko View Post
    Yes, that also helped.
    Thanks!
    Yous welcome, tzhanks for Rep Thiungy
    Wonk Wonk
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  2. #227
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by Tony Valko View Post
    Anyone know how to do this?

    I want to use a background fill color on some of the cells. How do I do that?

    I've seen some example code but I can't get it to work. Probably not putting it in the correct location.
    Hi Tony

    This?


    A
    B
    C
    1
    Carro
    Motor
    Cor
    2
    Carro1
    Motor1
    Cor1
    3
    Carro2
    Motor2
    Cor2
    4
    Carro3
    Motor3
    Cor3
    5
    Motor4
    Cor4
    6
    Cor5
    7
    8
    9
    Qt Carros
    Qt Motores
    Qt Cores
    10
    3
    4
    5
    11


    Updated version

    HTML Code: 
    Sub NewRngToBBCode()
        Dim DataObj As Object, strTable As String, ans As Long, bHeaders As Boolean
        
        Set DataObj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        'Application.CutCopyMode = False
        ans = MsgBox("Row and Column headers?", vbYesNo)
        
        If ans = 6 Then bHeaders = True
        
        strTable = "[size=1]" & RngToBBCodeNew(ActiveWindow.Selection, bHeaders) & "[/size]"
        DataObj.SetText strTable
        DataObj.PutInClipboard
    End Sub
    
    Public Function RngToBBCodeNew(rInput As Range, Optional bHeaders As Boolean = True) As String
        'Version that deals with hidden columns/rows and empty cells
        'Columns/Rows letters/numbers in Blue bold [b]...[/b]
        'Colums/Rows headers background color is light blue #DCE6F1
        'Get/set the background color of the cells
        Dim rRow As Range, rcell As Range, sReturn As String, strAux As String, strColor As String, strBgColor As String
        Dim strAlign As String
        
        sReturn = "[Table=""class: grid""]"
        
        If bHeaders Then
            sReturn = sReturn & "[tr][td=""bgcolor: #DCE6F1""][/td]" 'top left cell
            
            For Each rcell In rInput.Rows(1).Cells
                If rcell.EntireColumn.Hidden = False Then
                    'sReturn = sReturn & "[td][color=blue][CENTER][b]" & ColLetters(rcell.Column) & "[/b][/CENTER][/color][/td]"
                    sReturn = sReturn & "[td=""bgcolor: #DCE6F1""][color=blue][CENTER][b]" & ColLetters(rcell.Column) & "[/b][/CENTER][/color][/td]"
                End If
            Next rcell
            sReturn = sReturn & "[/tr]"
            
        End If
        
        For Each rRow In rInput.Rows
            If rRow.EntireRow.Hidden = False Then 'Check if the row is hidden
                sReturn = sReturn & vbNewLine & "[tr]"
                If bHeaders Then
                    'sReturn = sReturn & "[td][color=blue][CENTER][b]" & rRow.Row & "[/b][/CENTER][/color][/td]"
                    sReturn = sReturn & "[td=""bgcolor: #DCE6F1""][color=blue][CENTER][b]" & rRow.Row & "[/b][/CENTER][/color][/td]"
                End If
                
                For Each rcell In rRow.Cells
                    If rcell.EntireColumn.Hidden = False Then 'check if the column is hidden
                    
                    '------------------NEW NEW NEW get bgcolor
                    If rcell.Interior.Pattern <> xlNone Then
                        'Set colour
                        strAux = Right("000000" & Hex(rcell.Interior.Color), 6)
                        strBgColor = "#" & Right(strAux, 2) & Mid(strAux, 3, 2) & Left(strAux, 2)
                        strBgColor = "[td=""bgcolor:" & strBgColor & """]"
                    Else
                        strBgColor = "[td]"
                    End If 'close Interior Pattern <> xlNone
                    '------------------END NEW NEW NEW
                    
                    
                    If Len(CStr(rcell)) > 0 Then 'check if cell is not empty
                        'Set horizontal aligment
                        With rcell
                            Select Case .HorizontalAlignment
                                Case xlLeft
                                    strAlign = "LEFT"
                                Case xlCenter
                                    strAlign = "CENTER"
                                Case xlRight
                                    strAlign = "RIGHT"
                                Case Else
                                    Select Case VarType(.Value2)
                                        Case 8 'Sring
                                            strAlign = "LEFT"
                                        Case 10, 11 'Error or Boolean
                                            strAlign = "CENTER"
                                        Case Else 'Others
                                            strAlign = "RIGHT"
                                    End Select
                            End Select
                        End With
                                                   
                                                          
                        If rcell.Font.Color <> 0 Then
                            'Set colour
                            strAux = Right("000000" & Hex(rcell.Font.Color), 6)
                            strColor = "#" & Right(strAux, 2) & Mid(strAux, 3, 2) & Left(strAux, 2)
                        
                            'Build the BBCode string
                            sReturn = sReturn & strBgColor & "[COLOR=""" & strColor & """][" & strAlign & "]" & _
                            rcell.Text & "[/" & strAlign & "][/COLOR][/td]"
                        Else
                            'Build the BBcode string for a cell with color = Black (default)
                            sReturn = sReturn & strBgColor & "[" & strAlign & "]" & _
                            rcell.Text & "[/" & strAlign & "][/td]"
                        End If 'close Font.Color <> 0
                        
                    Else
                        'Build the BBCode string for an empty cell
                        sReturn = sReturn & strBgColor & "[/td]"
                    End If ' close Len>0
                        
                    End If ' close check column hidden
                Next rcell
            End If ' close check row hidden
            sReturn = sReturn & "[/tr]" & vbNewLine
        Next rRow
        
        sReturn = sReturn & "[/table]"
        RngToBBCodeNew = " " & vbNewLine & sReturn
    End Function
    
    Function ColLetters(lCol As Long) As String
        With ActiveSheet.Columns(lCol)
            ColLetters = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
        End With
    End Function
    Greetings

    M.
    Marcelo Branco

  3. #228
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - 2013
    Posts
    6,726

    Re: How to post a range - headers and data?

    Greetings Marcelo,

    While this thread is active again I have another question.

    After using the macro and that workbook is still open, I attempt to use it on another workbook the previous selection gets selected.

    How do we clear previous selections without closing that workbook?

    Thanks,
    Dave
    Dave

  4. #229
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: How to post a range - headers and data?

    Marcelo,

    Thanks for the updated code.

    I'll have to "tinker around" with it and see how it works.

    Hope Ford also sees this as he's been wanting to know which version of the code is the most up to date.

    Thanks!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #230
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,426

    Re: How to post a range - headers and data?

    @Dave

    Quote Originally Posted by FlameRetired View Post
    ......
    After using the macro and that workbook is still open, I attempt to use it on another workbook the previous selection gets selected.

    How do we clear previous selections without closing that workbook?.....
    That is weird ?? That has never happened to me with any BB Code Generator.
    Every time you run the code, the current selection is converted to BB Code and put in the clipboard... or so I thought. Make sure no codes anywhere are still running in deBug, or that nothing anywhere is waiting for a carriage return ( Enter ) - I have found that those two things prevent anything being copied to the clipboard. That could explain why it worked when you closed the previous File. - Maybe something was still "active" in that file which got killed when you closed it

    Alan
    _.................

    @Marcelo and Tony.
    That code form Marcelo works great..
    _.. added it to my “collection”
    https://app.box.com/s/zhz7awdag4nl1zs6564s9zzcwp50e4w9


    ( And dropped an Edit to Ford..
    http://www.excelforum.com/the-water-...ml#post4354756
    Now he’ll be really confused!!.. lol )

    Alan
    Last edited by Doc.AElstein; 04-04-2016 at 06:51 PM.

  6. #231
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - 2013
    Posts
    6,726

    Re: How to post a range - headers and data?

    Thanks, Alan.

    In previous versions resetting in VBA editor usually did the trick. Something happened along the way and that doesn't work for me anymore.

    If those things you listed are the issue it means I do that every time. That's a lot of coincidences. I also don't run macros / UDFs that often ... except for this one.

    Kept looking for a way to insert some kind of clearing code in the macro. Never found it. My knowledge of VBA is very thin ... I can spell VBA ... most days. LOL

    I've never found a link that covers this one. Ford raised the question early on, too. No response.

    Thanks again,
    Dave

  7. #232
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,426

    Re: How to post a range - headers and data?

    Hi Dave
    Quote Originally Posted by FlameRetired View Post
    .... previous versions resetting in VBA editor usually did the trick. Something happened along the way and that doesn't work for me anymore........
    I've never found a link that covers this one. Ford raised the question early on, too. No response....e
    Ok something weird then. Sorry i could not help. I am no VBA expert either.

    Maybe Marcelo or another one of the people who wrote these codes will get back to you on this one...
    _... But i do know that...
    _ in VBA, this normally clears the clipboard
    Application.CutCopyMode = False
    _ . and strangely i notice in Marcelo‘s most recent code he has this line, but he has it commented out...
    'Application.CutCopyMode = False

    And other codes have a
    Declare Function EmptyClipboard Lib "User32" () As Long
    And later in the code...
    _______ ' Clear the Clipboard.
    ______ X = EmptyClipboard()

    But i am out of my depth here and do not know what all that stuff is about.... !!! I only ever did very simple mods to the Codes from the Pros
    _........

    Hope you get it sorted..

    Alan

    P.s.I can tell you one other weird thing about Add-Ins to be careful of:
    I sometimes found that when i was trying out the various Add-Ins that sometimes VBA did weird things and decided itself which Add-In to run, regardless of the one I had selected.
    And the File name and the name seen in the Add-In list was sometimes different. So I sometimes found it was necessary to delete all BB Code Add-ins ( everywhere ! ) on my computer except the one i wanted to use so as to be sure that VBA “chose” the one i wanted!!). That is one reason why i went a bit away fron the Add-Ins, and turned them into “Stand alone codes”.. which I have a messy collection of in the file i linked.

  8. #233
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Using the code in two workbooks

    New version - observe the code line
    Application.CutCopyMode = False


    HTML Code: 
    Sub NewRngToBBCode()
        Dim DataObj As Object, strTable As String, ans As Long, bHeaders As Boolean
        
        Set DataObj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        Application.CutCopyMode = False
        ans = MsgBox("Row and Column headers?", vbYesNo)
        
        If ans = 6 Then bHeaders = True
        
        strTable = "[size=1]" & RngToBBCodeNew(ActiveWindow.Selection, bHeaders) & "[/size]"
        DataObj.SetText strTable
        DataObj.PutInClipboard
    End Sub

    Testing...

    Workbook 1


    A
    B
    1
    Names
    Scores
    2
    Anthony
    10
    3
    John
    20
    4
    Mary
    30
    5
    Richard
    40
    6
    Robert
    50


    Workbook2


    A
    B
    1
    SKU
    Price
    2
    1001
    10
    3
    1002
    12
    4
    1003
    14
    5
    1004
    16
    6
    1005
    18

  9. #234
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - 2013
    Posts
    6,726

    Re: How to post a range - headers and data?

    Thank you, Marcelo.

    I'm clueless, though.

    Please tell me what I need to do with this? Where do I put what?

    Do I need to edit / rename anything besides insert the "Application.CutCopyMode = False" line?


    My VBA knowledge is zero.

    Thank you, again.

    Dave

  10. #235
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - 2013
    Posts
    6,726

    Re: How to post a range - headers and data?

    @ Doc.AElstein

    Thank you, again.

    Maybe Marcelo will enlighten us both.

    I think I need a "seeing-eye-programmer"! LOL

    Dave

  11. #236
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Put the code in Personal Workbook - see the link below

    How to create a Personal Workbook
    http://www.rondebruin.nl/win/personal.htm

    You can find in the internet many tutorials/videos about VBA
    Some links
    http://www.easyexcelvba.com/introduction.html

    http://chandoo.org/wp/2011/08/29/int...to-vba-macros/

    http://www.ozgrid.com/Excel/free-tra...ba1lesson1.htm

  12. #237
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Ignore this post
    Last edited by mlcb; 04-04-2016 at 10:47 PM.

  13. #238
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    41,417

    Re: How to post a range - headers and data?

    Quote Originally Posted by FlameRetired View Post
    Greetings Marcelo,

    While this thread is active again I have another question.

    After using the macro and that workbook is still open, I attempt to use it on another workbook the previous selection gets selected.

    How do we clear previous selections without closing that workbook?

    Thanks,
    Dave
    I have the same problem- sometimes, but only at home where I use W10 and office 13 (office 16 now)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think some-1 helped you

    Regards
    Ford

  14. #239
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    41,417

    Re: How to post a range - headers and data?

    From Tony...
    Hope Ford also sees this as he's been wanting to know which version of the code is the most up to date.
    yes I did, thank you

    From Doc (Alan)...
    ( And dropped an Edit to Ford..
    http://www.excelforum.com/the-water-...ml#post4354756
    Now he’ll be really confused!!.. lol )
    confused is my middle name - so nothing new there lol

    OK with that new code from Marcelo (post 227), do I replace whatever I had for posting tables, with that code?

  15. #240
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,426

    Re: How to post a range - headers and data?

    Quote Originally Posted by FDibbins View Post
    ......
    OK with that new code from Marcelo (post 227), do I replace whatever I had for posting tables, with that code?
    Hi Ford.
    Not sure who you are asking. And i am nor sure what "code" ( or Add-In ) you are currently using.
    But i just had another go at trying to clarify things a bit as far as i understand them for you:
    http://www.excelforum.com/the-water-...ml#post4355240
    Alan
    Last edited by Doc.AElstein; 04-05-2016 at 08:11 AM.

+ Reply to Thread
Page 16 of 17 FirstFirst ... 6 14 15 16 17 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1