BBCode Table

**pike** · 05-16-2016, 04:19 AM

there you go font name added .. but will default if the worksheet font name is not avalibale in the forum BBcode font list

Code:

Option Explicit
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

Sub BB_Table_Clipboard()
    Dim BB_Row As Range, BB_Cells As Range, BB_Range As Range
    Dim BB_Code As String, strFontColour As String, strBackColour As String, strAlign As String, strWidth As String
    Dim strFontName As String
    Set BB_Range = Selection
    BB_Code = "[table=" & """" & "class:thin_grid" & """" & "]" & vbNewLine
    BB_Code = BB_Code & "[tr][td][font=Wingdings]v[/font][/td]" & vbNewLine
    For Each BB_Cells In BB_Range.Rows(1).Cells
        strWidth = Application.WorksheetFunction.RoundUp(BB_Cells.ColumnWidth * 7.5, 0)
        BB_Code = BB_Code & "[td=" & """" & "bgcolor:#ECF0F0, align:center,width:" & strWidth & """" & "][B]" & Split(BB_Cells.Address, "$")(1) & "[/B][/td]" & vbNewLine
    Next BB_Cells
    BB_Code = BB_Code & "[/tr]"
    For Each BB_Row In BB_Range.Rows
        BB_Code = BB_Code & "[tr]"
        BB_Code = BB_Code & "[td=" & """" & "bgcolor:#ECF0F0, align:center" & """" & "][B]" & BB_Row.Row & "[/B][/td]" & vbNewLine
        For Each BB_Cells In BB_Row.Cells
            strFontColour = objColour(BB_Cells.Font.Color)
            strBackColour = objColour(BB_Cells.Interior.Color)
            strAlign = FontAlignment(BB_Cells)
            strFontName = BB_Cells.Font.Name
            BB_Code = BB_Code & "[td=" & """" & "bgcolor:" & strBackColour & ", align:" & strAlign & """" & "][COLOR=""" & strFontColour & """][Font=""" & strFontName & """]" & IIf(BB_Cells.Font.Bold, "[B]", "") & BB_Cells.Text & IIf(BB_Cells.Font.Bold, "[/B]", "") & "[/Font][/COLOR][/td]" & vbNewLine
        Next BB_Cells
        BB_Code = BB_Code & "[/tr]" & vbNewLine
    Next BB_Row
    BB_Code = BB_Code & "[/table]"
    ClipBoard_SetData (BB_Code)
    Set BB_Range = Nothing
End Sub

Function objColour(strColour As String) As String
    objColour = "#" & Right(Right("000000" & Hex(strColour), 6), 2) & Mid(Right("000000" & Hex(strColour), 6), 3, 2) & Left(Right("000000" & Hex(strColour), 6), 2)
End Function

Function FontAlignment(ByVal objCell As Object) As String
    With objCell
        Select Case .HorizontalAlignment
        Case xlLeft
            FontAlignment = "LEFT"
        Case xlRight
            FontAlignment = "RIGHT"
        Case xlCenter
            FontAlignment = "CENTER"
        Case Else
            Select Case VarType(.Value2)
            Case 8
                FontAlignment = "LEFT"
            Case 10, 11
                FontAlignment = "CENTER"
            Case Else
                FontAlignment = "RIGHT"
            End Select
        End Select
    End With
End Function

Function ClipBoard_SetData(MyString As String)
    Dim hGlobalMemory As Long, lpGlobalMemory As Long
    Dim hClipMemory As Long, X As Long

    hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)
    lpGlobalMemory = GlobalLock(hGlobalMemory)
    lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)
    If GlobalUnlock(hGlobalMemory) <> 0 Then
        MsgBox "Could not unlock memory location. Copy aborted."
        GoTo OutOfHere2
    End If
    If OpenClipboard(0&) = 0 Then
        MsgBox "Could not open the Clipboard. Copy aborted."
        Exit Function
    End If
    X = EmptyClipboard()
    hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
OutOfHere2:
    If CloseClipboard() = 0 Then
        MsgBox "Could not close Clipboard."
    End If
End Function

**DocAElstein** · 05-16-2016, 02:04 PM

Hi Pike...

Originally Posted by pike

there you go font name added .. but will default if the worksheet font name is not avalibale in the forum BBcode font list.....

Thanks pike, another one for the collection

Using Excel 2007

_Row\^Col	F	G
14	PikeCalibri	Fooaarrnst Arial Narrow
15	Verdana	Batang

Sheet: Molly

Full Version for your Code which allows one to paste a screenshot of a Spreadsheet range, in a form that can be copyied to a Spreadsheet is here:
http://www.excelfox.com/forum/showth...=9804#post9804

File with this code and many others:
https://app.box.com/s/zhz7awdag4nl1zs6564s9zzcwp50e4w9

_.......................

Alan

_......

|Size = 2| below ( |size = 0| above ), maybe most prefer - I need small to get all my errlabberations in !

Using Excel 2007