# Off Topic > Suggestions for Improvement >  > [SOLVED] How to post a range - headers and data?

## mlcb

Hi,

How to post a range of my worksheet keeping the alignment of the cells (rows/columns)?

I tried using IE10: select range; put borders; copy; paste in the forum reply page. 

But it didn't work as expected - the cells were not aligned with headers and the spacing was messed up.

I tried also Go Advanced > Table but i could see only html tags...

Am i missing something or is always preferable attach a file?

Thanks in advance for any help.

Marcelo

----------


## protonLeah

The easiest way is to use [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags.  You might have to make slight adjustments.




```
Please Login or Register  to view this content.
```

----------


## mlcb

Ben,

Thank you for the tip. 

I'll try the CODE tags. 

MArcelo

----------


## mlcb

Just testing




```
Please Login or Register  to view this content.
```


It seems ok  :Smilie: 

Thanks again, Ben

----------


## Tony Valko

Let me test a couple of samples...




```
Please Login or Register  to view this content.
```


If I make that 1st column wider:




```
Please Login or Register  to view this content.
```


As you can see it only looks OK when the data is of a relatively uniform width.

----------


## mlcb

Hi Biff,

Nice to "see" you 

Yes, I understand what you are saying. It seems that not always is possible to post a nice and understandable table using copy and paste.

Do you know how to do tables like these?
http://www.excelforum.com/excel-form...ple-cells.html

----------


## Tony Valko

Unfortunately, I don't know how to make those kinds of tables. Wished I did.

They use html table tags.

*EDIT:* Correction. They use the BB code tags.

At that link, on the top post, click the "Reply With Quote" button and you'll see the code used to produce the tables.  :EEK!: 

I don't understand it at all!  :Confused: 

If anyone that reads this post knows how to make those kinds of tables how about posting a little tutorial for us?

----------


## FDibbins

Editing that thread to see what they did gives info that to me, make it seem easier to just post a sample W/B lol...
(I may end up editing/posting a cpl of times to show the code)

[table="width: 200, class: grid, align: left"     "]"
[tr]
	[td]cat[/td]
	[td]pet store 1[/td]
[/tr]
[tr]
	[td]dog[/td]
	[td]pet store 1[/td]
[/tr]

and that was just for the 1st 2 rows!!

----------


## protonLeah

tr = table row
td = table data (cell)

the [td][/td] 's must be  wrapped by [tr][/tr] in all cases.  Note that the data to be displayed must be within [td][/td] tags (e.g.) [td]Ben[/td]

here's one with two rows, 3 columns each



```
Please Login or Register  to view this content.
```


results in:











here's one with three rows, row one has tow columns, row two has only one and row three has five:





```
Please Login or Register  to view this content.
```


resulting in:

----------


## FDibbins

Thanks Ben - like I said - almost easier to post a sample workbook  :Smilie:

----------


## mlcb

TESTING

< Table > <tr><td> </td><td>A</td><td>B</td></tr> < tr > <td>1</td><td>Name</td><td>Scores</td></tr>
 < tr > <td>2</td><td>John</td><td>30</td></tr>
 < tr > <td>3</td><td>Mary</td><td>32</td></tr>
 < tr > <td>4</td><td>Mike</td><td>34</td></tr>
 < tr > <td>5</td><td>William</td><td>36</td></tr>
</table>

I found this in the internet
http://dailydoseofexcel.com/archives...-from-a-range/

But...no success... :Frown:

----------


## shg

I use the add-in posted at http://www.excelforum.com/tips-and-t...r-posting.html

----------


## mlcb

Name
Scores

John
30

Mary
32

Mike
34

William
36



With some adjustment in the code...

*Success!*

*New code*




```
Please Login or Register  to view this content.
```

----------


## mlcb

I don't know why the code is not appearing correctly  :Confused: 

I used CODE tags, but something is wrong (???)

----------


## FDibbins

your code looked fine to me?

----------


## mlcb

> your code looked fine to me?



Can you see the entire code? I cannot (???) ---> using IE10 

I can see only using Reply With Quote. Is it a normal behavior?

----------


## Tony Valko

Let me try Marcelo's code...


A
B
C
D

1
Name
Region
Sales


2
Pat
North
100


3
Put
East
200


4
Pit
South
300


5
Pot
West
400

----------


## Tony Valko

Hmmm...

I messed up and made the range A1:D5 which is why that empty column is in there.

Here it is again with the correct range A1:C5...


A
B
C

1
Name
Region
Sales

2
Pat
North
100

3
Put
East
200

4
Pit
South
300

5
Pot
West
400

----------


## Tony Valko

This looks very promising.

Nice job, Marcelo!  :Cool:

----------


## Tony Valko

> Can you see the entire code? I cannot (???) ---> using IE10 
> 
> I can see only using Reply With Quote. Is it a normal behavior?



No, I can't see it either in IE9 but I was able to get all of it doing the "Reply With Quote" trick.

----------


## Tony Valko

Some more testing...

Pat
North
100

Put
East
200

Pit
South
300

Pot
West
400

----------


## Tony Valko

More testing...

Name
Date
In
Out

Pat
8/24/2013
7:05 AM
3:55 PM

Put
8/24/2013
6:48 AM
3:05 PM

Pit
8/24/2013
7:00 AM
3:30 PM

Pot
8/24/2013
7:08 AM
3:17 PM




Name
Date
In
Out

Pat
8/24/2013
7:05 AM
3:55 PM

Put
8/24/2013
6:48 AM
3:05 PM

Pit
8/24/2013
7:00 AM
3:30 PM

Pot
8/24/2013
7:08 AM
3:17 PM

----------


## mlcb

> This looks very promising.
> 
> Nice job, Marcelo!



Wow... it works!!!

Thank you, Tony

M.

----------


## Tony Valko

Some more testing...

Name
Date
In
Out

Pat
8/24/2013
7:05 AM
3:55 PM

Put
8/24/2013
6:48 AM
3:05 PM

Pit
8/24/2013
7:00 AM
3:30 PM

Pot
8/24/2013
7:08 AM
3:17 PM

----------


## mlcb

Testing using Font colors


A
B

1
Name
Scores

2
John
30

3
Mary
32

4
Mike
34

5
William
36

----------


## mlcb

Follows the code

Use *Reply with Quote* to see the entire code




```
Please Login or Register  to view this content.
```

----------


## Tony Valko

a
b

1
9
59

2
10
37

3
8
76

4
83
95

5
49
6

----------


## Tony Valko

a
b

1
9
59

2
10
37

3
8
76

4
83
95

5
49
6

----------


## Tony Valko

Wonder why the column headers are showing in lowercase?

----------


## Tony Valko

A
B

1
Data
Data

2
Data
Data

3
Data
Data

4
Data
Data

5
Data
Data



 :Confused:

----------


## mlcb

a
b
c

1
1
9
59

2
2
10
37

3
3
8
76

4
4
83
95

5
5
49
6



I'll check the code  :Confused:

----------


## mlcb

Blah Blah


A
B
C

1
3
2
4

2
1
9
59

3
2
10
37

4
3
8
76

5
4
83
95

6
5
49
6

----------


## mlcb

a
b
c

1
3
2
4

2
1
9
59

3
2
10
37

4
3
8
76

5
4
83
95

6
5
49
6



The code generates letters in uppercase, but If the table is first thing in the post, for some reason beyond my imagination, the column letters appear in lowercase.

 :Confused:   :Confused:

----------


## mlcb

Data Range

A
B
C

1
3
2
4

2
1
9
59

3
2
10
37

4
3
8
76

5
4
83
95

6
5
49
6




I just changed the last code line to
RangeToHTML = "Data Range" & vbNewLine & sReturn

----------


## mlcb

*CORRECTION*

The previous codes are not setting correctly the Font colors

*New code*




```

```

----------


## mlcb

Tony,

Do you mind, if you have time, to do a test?

1. Create a Module in your Personal Workbook and put the Sub and the Function below in the new module
(You must add a Reference to Microsoft Forms 2.0 Object Library) 




```

```


2. Add a new command (Macros) in the QAT, linking to the Sub CopyRngToHTML in your Personal Workbook.

Then to post a table all you have to do is:
Select the Range
Click in the new icon (QAT)
Paste in the Forum Reply page.

It worked for me. Please, check if it works to you too.

M.

----------


## mlcb

More testing (user defines table width via Input Box)

Data Range

A
B

1
Name
Scores

2
John
30

3
Mary
32

4
Mike
34

5
William
36

----------


## Tony Valko

> Tony,
> 
> Do you mind, if you have time, to do a test?



Here goes...

Data Range

A
B

1
Name
Status

2
Pit
Pass

3
Pat
Fail

4
Pot
Fail

5
Put
Pass



*EDIT:* Tested in Excel 2010

----------


## Tony Valko

> More testing (user defines table width via Input Box)



Yeah, I like having that option.

That way you can show more columns in the post without having to scroll. In some of my other test posts I was just manually editing this line:

[Table="width: 500, class: grid"]

----------


## mlcb

> Yeah, I like having that option.
> 
> That way you can show more columns in the post without having to scroll. In some of my other test posts I was just manually editing this line:
> 
> [Table="width: 500, class: grid"]




Testing new version - user defines width=300

Data Range

A
B
C

1
3
2
4

2
1
9
59

3
2
10
37

4
3
8
76

5
4
83
95

6
5
49
6




New code to Personal Workbook




```

```



*Next improvement*: I think (not tested yet) the code doesn't work with double or triple letters columns (AA or AAA), but I can envisage an easy solution

----------


## Tony Valko

Test with input box...

Data Range

A
B

1
Name
Score

2
Biff
71

3
Tom
77

4
Paul
82

5
Hoppy
88

----------


## Tony Valko

Data Range

A
B

1
Name
Score

2
Biff
71

3
Tom
77

4
Paul
82

5
Hoppy
88

----------


## shg

Can you set the justification according to cell contents?

HTML5 apparently doesn't accept align=right|left|center

----------


## mlcb

Data Range

A
B
C

1
3
2
4

2
1
9
59

3
2
10
37

4
3
8
76

5
4
83
95

6
5
49
6



I aligned only the first row ---> center. 
I did it manually but I think is possible to include in the code (to all rows)
Next improvement...

----------


## shg

I meant alignment by cell, Marcelo -- text left, numbers right, errors and Booleans center.

----------


## mlcb

*EDIT:Hadn't seen #45*

shg

Columns and Rows and Booleans---> Center
Numbers ---> Right
Text ---> Left

Is it? 

Requires some IFs to check cell contents...I'll try soon

M.

----------


## shg

> Requires some IFs to check cell contents



Marcelo,

VarType(cell.Value2) always returns vbEmpty, vbDouble, vbString, vbBoolean, or vbError

----------


## mlcb

shg

What do you suggest if the cell has a number formatted as text? Something like
'1000

I was thinking in IsNumeric, but I think it will result True for numbers as Numbers and also to numbers as Text

Any suggestion?

M.

----------


## mlcb

> Marcelo,
> 
> VarType(cell.Value2) always returns vbEmpty, vbDouble, vbString, vbBoolean, or vbError



What VarType returns for '1000? vbDouble or vbString?

M.

----------


## mlcb

I checked: returns 8 ---> Text

M.

----------


## shg

....^....1

----------


## shg

I would do what Excel does by default -- left-align numeric strings, like all other text.

----------


## Tony Valko

Tested in Excel 2002...

Data Range

A
B
C

1
Date
Debit
Credit

2
8/1/2013

21.00

3
8/2/2013
51.00


4
8/5/2013
80.00
82.00

5
8/8/2013
12.00


6
8/8/2013
55.00
52.00

7
8/15/2013
65.00
44.00

8
8/16/2013
35.00
61.00

9
8/16/2013

91.00

10
8/26/2013
69.00
35.00

----------


## Simon Lloyd

I've not tried it but in Excel 2010> you can select a range>file>save as>file type mhtml dont click save but click publish, you could then use the source code from the resulting mhtml page.

----------


## mlcb

> I would do what Excel does by default -- left-align numeric strings, like all other text.



Ok, i'll try tonight in my next version (i have to work a little bit on my job or ...... FIRED  :Mad: )
 :Smilie: 


New version dealing with 2/3 letters columns (added a new function)

Data Range

Z
AA
AB

1
Name
Score
Rank

2
Anthony
34
2

3
John
30
5

4
Mary
33
3

5
Mike
38
1

6
Robert
32
4






```

```

----------


## Tony Valko

Just did a copy/paste back to Excel test to make sure no html "junk" shows up in the empty cells.

Looks good!  :Smilie:

----------


## mlcb

oops i copied the old Sub

This is the correct version




```

```

----------


## mlcb

> I've not tried it but in Excel 2010> you can select a range>file>save as>file type mhtml dont click save but click publish, you could then use the source code from the resulting mhtml page.



I tried in Excel 2007 
The resulting table code:

<table border=0 cellpadding=0 cellspacing=0 width=192 style='border-collapse:
 collapse;table-layout:fixed;width:144pt'>
 <col width=64 span=3 style='width:48pt'>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl159881 width=64 style='height:15.0pt;width:48pt'>Name</td>
  <td class=xl159881 width=64 style='width:48pt'>Score</td>
  <td class=xl159881 width=64 style='width:48pt'>Rank</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl159881 style='height:15.0pt'>Anthony</td>
  <td class=xl159881 align=right>34</td>
  <td class=xl159881 align=right>2</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl649881 style='height:15.0pt'>John</td>
  <td class=xl649881 align=right>30</td>
  <td class=xl649881 align=right>5</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl159881 style='height:15.0pt'>Mary</td>
  <td class=xl159881 align=right>33</td>
  <td class=xl159881 align=right>3</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl639881 style='height:15.0pt'>Mike</td>
  <td class=xl639881 align=right>38</td>
  <td class=xl639881 align=right>1</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl159881 style='height:15.0pt'>Robert</td>
  <td class=xl159881 align=right>32</td>
  <td class=xl159881 align=right>4</td>
 </tr>
 <![if supportMisalignedColumns]>
 <tr height=0 style='display:none'>
  <td width=64 style='width:48pt'></td>
  <td width=64 style='width:48pt'></td>
  <td width=64 style='width:48pt'></td>
 </tr>
 <![endif]>
</table>

Am i missing something?

----------


## Tony Valko

This is already a great tool and I will be using it extensively (have used it several times today already).

Hats off to Marcelo for the work he has done on this!  :Cool:

----------


## mlcb

> This is already a great tool and I will be using it extensively (have used it several times today already).
> 
> Hats off to Marcelo for the work he has done on this!



Tony,

*Thank you very much for your kind words.* 

I am very happy for creating a tool, i think, will be very useful for many members.

But, as shg asked for a more sophisticated version, i.e., aligning according cells contents, here it goes:
Numbers --> Right
Text --> Left
Boolean --> Center


Data Range

A
B
C
D

1
Name
Scores
V/F
Date

2
John
30
VERDADEIRO
01/01/2013

3
Mary
33
FALSO
02/01/2013

4
Mike
34
FALSO
03/01/2013

5
William
36
VERDADEIRO
04/01/2013



Portuguese  --  English
VERDADEIRO ---> TRUE
FALSO      ---> FALSE

Code to Personal Workbook




```

```


That's it  :Smilie: 

M.

----------


## mlcb

Test 
Numbers as Text (red)
Numbers as Numbers (blue)

Data Range

N
O

1
Invoice#
Value

2
0001
100

3
0002
110

4
0003
120

----------


## mlcb

Test with dates (fake/real)

Data Range

AD
AE

1
Date (text)
Real Dates

2
jun-01
jun-01

3
jun-02
jun-02

4
jun-03
jun-03

----------


## mlcb

Is possible to center the headers (first data row) if any. Would require another question to the user.

Your data have headers?

It could be done through a Message Box - very easy but maybe boring (2 questions...).

Or, more sophisticated: by a mini-UserForm (asking about table width and data headers) created on-the-fly and deleted when the routine ends. 

I do not think it's worth it. Too much work for little gain ... (any volunteer?)

M.

----------


## Tony Valko

Personally, alignment is not that important to me.

In the Excel files I find it easier to read if everything (dates, numbers, text, Booleans, errors) is aligned right.

----------


## mlcb

Maybe important for help some guys trying to MATCH/VLOOKUP numbers against data range containing fake numbers or dates (text). 

If they use this tool, would be very easy to identify the problem.

----------


## snb

In that case you'd better ask them to post a sample workbook....

----------


## mlcb

Yes, the facility of attach a workbook is probably the best option.

But as i usually do tests in the same workbook for different threads, and also as some forums don't allow file uploading, i think this simple tool can be helpful in some cases.

----------


## Tony Valko

When you're done tweaking the code and have arrived at the final version I suggest that you (or someone that wants to do it) write a tutorial on how to apply this tool. The tutorial title could be something like: "How to post sample data in a reply".

For example, some folks may not know how to set the reference to the MS Forms 2.0 Object Library. They may not know how/where to insert a new module. Etc., etc., etc.

Then, I would request to the mods/admins that this tutorial be "pinned" to the top of the Tips /Tutorials forum so that it will always be easy to locate.

----------


## mlcb

Tony,

I think that would be best suited to someone who has English as his native language

Some ideas:

*- Useful links*

*Getting Started with VBA*
http://msdn.microsoft.com/en-us/library/ee814737 (v = office.14). aspx

*How to Create and update your Personal Workbook*
http://office.microsoft.com/en-001/e...102174076.aspx


*- Tips*

*How to add a new Module to your Personal Workbook*
Alt + F11 to open the VBA Editor
On the left panel click in VBAProject (Personal.xlsb)
Go to Insert menu and pick Module

*How to add a Reference to Microsoft Forms 2.0 Object Library*
Alt + F11 to open the VBA Editor
Go to Tools menu pick References
Press the Search button
type in box
FM20.dll
press the button Open


Follows the most recent and updated version




```

```


Hope that someone can do the job.

Regards,

Marcelo

----------


## mlcb

Another useful link

How to Add a Macro to the Quick Access Toolbar
http://www.dummies.com/how-to/conten...ck-access.html

----------


## Tony Valko

One last test...

*
Data Range

A
B
C
D

2
100

300
8

3
7




4
200




5
2




6
300




7
8




8
400




9
9




10
500




11
6




12






*

----------


## mlcb

Please, disregard

----------


## mlcb

Tony,

You don't like the version with data cell alignment. Ok, but I think you can use this version with column letters and row numbers centered.

*

Data Range

A
B
C
D

2
100

300
8

3
7




4
200




5
2




6
300




7
8




8
400




9
9




10
500




11
6





*




```

```

----------


## shg

Seems to me it should be implemented as an add-in, Marcelo, which I'd be happy to do and post.

I added some code that respects cell horizontal alignment, and defaults to Excel's default alignment if set to "General"

I'd like to figure out how to automate the width argument. For my standard font (Calibri 9) and all cells set to auto width with no wrapping, 1.9 * selection.width seems to work OK, but that seems clunky. Is there any HTML tag that says to autosize the table width without text wrapping?

----------


## mlcb

> Seems to me it should be implemented as an add-in, Marcelo, which I'd be happy to do and post.
> 
> I added some code that respects cell horizontal alignment, and defaults to Excel's default alignment if set to "General"
> 
> I'd like to figure out how to automate the width argument. For my standard font (Calibri 9) and all cells set to auto width with no wrapping, 1.9 * selection.width seems to work OK, but that seems clunky. Is there any HTML tag that says to autosize the table width without text wrapping?



shg,

Feel free to add any feature and do improvements and post it. 

I would appreciate very much!

About your question related to auto-size: my knowledge about HTML is almost none. I only know the basic of the first versions, i.e., I know nothing!  :Confused: 

Questions: 
1. i tried many ways (to no avail) to set the background color of the cells. Could you envisage a solution?

2. Is there a VBA limit to the size of the string variable sReturn used inside the Function?

----------


## shg

1. Uhhh ... no. I've learned 90% of what I know of HTML from your code, Marcelo.

2. As big as you have memory for. (I just generated a 134MB string)

----------


## shg

> the code doesn't work with double or triple letters columns (AA or AAA),






```
Please Login or Register  to view this content.
```


E.g., =ColLtr(337973)

----------


## mlcb

> ```
> Please Login or Register  to view this content.
> ```
> 
> 
> E.g., =ColLtr(337973)



I was talking about my first version. The present version works fine with 2/3 letters columns using this function




```

```



Data Range

ZZ
AAA
AAB

1
Name
Scores
Rank

2
John
30
4

3
Mary
33
3

4
Mike
34
2

5
William
36
1

----------


## mlcb

> 1. Uhhh ... no. I've learned 90% of what I know of HTML from your code, Marcelo.



Like Apollo 13 the only thing I can say is:

*Houston, we have a problem!*
 :Smilie: 





> 2. As big as you have memory for. (I just generated a 134MB string)



Good news!

M.

----------


## mlcb

Finally I spotted the thread from where I got the conversion function -: Number to Column Letter(s) - and copied to my Personal Workbook.

http://www.mrexcel.com/forum/excel-q...er-letter.html

Thank you VoG!

----------


## mlcb

> 1. Uhhh ... no. I've learned 90% of what I know of HTML from your code, Marcelo.



shg, 

I am (we are) NOT using HTML. We are using BB Codes that is very limited when compared to HTML.

See
http://www.bbcode.org/

At a forum specific page (not at the forum open page) , at right bottom , you can find a box Forum Permissions and a link: BB code is On
http://www.excelforum.com/misc.php?do=bbcode

or

http://www.mrexcel.com/forum/misc.php?do=bbcode

Therefore it means that we cannot think in sophisticated features.

M.

----------


## Tony Valko

> Tony,
> 
> You don't like the version with data cell alignment. Ok, but I think you can use this version with column letters and row numbers centered.
> 
> 
> 
> 
> 
> ```
> ...



When I try that code I get a Compile Error: Sub or Function not defined.

In this line of code:




```

```


ColLetters is highlighted as the offending item.

----------


## Tony Valko

> Therefore it means that we cannot think in sophisticated features.



The more computing I do the more I find that to be true.

Seems as though almost nothing goes perfectly as planned! There's always some little glitch that you have to deal with (if you CAN deal with it at all).

----------


## mlcb

> When I try that code I get a Compile Error: Sub or Function not defined.
> 
> In this line of code:
> 
> 
> 
> 
> ```
> 
> ...



Add this function to the Module - so the main function can deal with 2/3 letters columns.




```

```

----------


## Tony Valko

Data Range

A
B
C
D
E

1
ya
ba
da
ba
do

2
81
53
5
24
70

3
81
77
71
34
89

4
20
58
42
25
72

5
63
50
12
71
31






Data Range

ZY
ZZ
AAA
AAB
AAC

1
ya
ba
da
ba
do

2
81
53
5
24
70

3
81
77
71
34
89

4
20
58
42
25
72

5
63
50
12
71
31

----------


## Tony Valko

That fixed it.

Looks good!  :Cool:

----------


## mlcb

Tony,

Two great improvements suggested by Rory (Mr. Excel Forum) and implemented

1. Table autosize
No need of the InputBox asking table widht

2. No need to add a new Reference
Uses late binding




Data Range

A
B
C
D
E

1
Name
Dept
Date
Score
Rank

2
Anthony
Dept1
28/08/2013
30
6

3
John
Dept2
28/08/2013
32
4

4
Mark
Dept3
28/08/2013
31
5

5
Mary
Dept4
28/08/2013
34
2

6
Michael
Dept5
28/08/2013
35
1

7
Robert
Dept6
28/08/2013
33
3




*New code*



```

```

----------


## mlcb

Tony and shg,

Now i think we have a workable tool that could be posted to the community.

Providing the links about Personal Workbook and How to Add a command (macro) to QAT, I think that even an average user can easily install and use it.

I'm a new guy in this forum, so maybe you can do the suggestion to the mods.

New improvements, provided by shg, could be added at any moment.

Is it ok to you two?

M.

----------


## Tony Valko

Testing the latest version...

I added a little tweak to make the column and row headers appear in bold.

Data Range

*A*
*B*
*C*
*D*
*E*

*1*
Dates
Numbers
Text
Logicals
Errors

*2*
5/15/2004
-0.255
Ya
TRUE
#N/A

*3*
12/26/2004
16
Ba
FALSE
#REF!

*4*
11/7/2001
1.00E+100
Da

#NUM!

*5*
8/18/2007
2
Ba

#VALUE!

*6*
2/8/2011
8888
Do

#NULL!

*7*
12/25/2005
2.814
I

#NAME?

*8*
6/15/2013
7
C



*9*
1/28/2007
1.00
U

----------


## Tony Valko

Using a smaller font...


Data Range

*A*
*B*
*C*
*D*
*E*

*1*
Dates
Numbers
Text
Logicals
Errors

*2*
5/15/2004
-0.255
Ya
TRUE
#N/A

*3*
12/26/2004
16
Ba
FALSE
#REF!

*4*
11/7/2001
1.00E+100
Da

#NUM!

*5*
8/18/2007
2
Ba

#VALUE!

*6*
2/8/2011
8888
Do

#NULL!

*7*
12/25/2005
2.814
I

#NAME?

*8*
6/15/2013
7
C



*9*
1/28/2007
1.00
U

----------


## Tony Valko

Looks very good to me.

I've already used it in several posts the last couple of days.

 :Cool:

----------


## mlcb

I forgot align errors = center 

Need a small adjustment in Select Case




```

```

----------


## Tony Valko

Testing the latest version...

Data Range

*A*
*B*
*C*
*D*
*E*

*1*
Dates
Numbers
Text
Logicals
Errors

*2*
5/15/2004
-0.255
Ya
TRUE
#N/A

*3*
12/26/2004
16
Ba
FALSE
#REF!

*4*
11/7/2001
1.00E+100
Da

#NUM!

*5*
8/18/2007
2
Ba

#VALUE!

*6*
2/8/2011
8888
Do

#NULL!

*7*
12/25/2005
2.814
I

#NAME?

*8*
6/15/2013
7
C



*9*
1/28/2007
1
U





Using a smaller font...


Data Range

*A*
*B*
*C*
*D*
*E*

*1*
Dates
Numbers
Text
Logicals
Errors

*2*
5/15/2004
-0.255
Ya
TRUE
#N/A

*3*
12/26/2004
16
Ba
FALSE
#REF!

*4*
11/7/2001
1.00E+100
Da

#NUM!

*5*
8/18/2007
2
Ba

#VALUE!

*6*
2/8/2011
8888
Do

#NULL!

*7*
12/25/2005
2.814
I

#NAME?

*8*
6/15/2013
7
C



*9*
1/28/2007
1
U






Looks good!

----------


## Tony Valko

One more time...

Data Range

*A*
*B*
*C*
*D*
*E*

*1*
Dates
Numbers
Text
Logicals
Errors

*2*
5/15/2004
-0.255
Ya
TRUE
#N/A

*3*
12/26/2004
16
Ba
FALSE
#REF!

*4*
11/7/2001
1.00E+100
Da

#NUM!

*5*
8/18/2007
2
Ba

#VALUE!

*6*
2/8/2011
8888
Do

#NULL!

*7*
12/25/2005
2.814
I

#NAME?

*8*
6/15/2013
7
C



*9*
1/28/2007
1
U







Data Range

*A*
*B*
*C*
*D*
*E*

*1*
Dates
Numbers
Text
Logicals
Errors

*2*
5/15/2004
-0.255
Ya
TRUE
#N/A

*3*
12/26/2004
16
Ba
FALSE
#REF!

*4*
11/7/2001
1.00E+100
Da

#NUM!

*5*
8/18/2007
2
Ba

#VALUE!

*6*
2/8/2011
8888
Do

#NULL!

*7*
12/25/2005
2.814
I

#NAME?

*8*
6/15/2013
7
C



*9*
1/28/2007
1
U

----------


## mlcb

Test with text with spaces

Data Range

*A*
*B*

*1*
Product Number
Part Number

*2*
Product 1
Part 10

*3*
Product 1
Part 11

*4*
Product 1
Part 12

*5*
Product 2
Part 13

*6*
Product 2
Part 14

----------


## mlcb

> Seems to me it should be implemented as an add-in, Marcelo, which I'd be happy to do and post.
> 
> I added some code that respects cell horizontal alignment, and defaults to Excel's default alignment if set to "General"
> 
> I'd like to figure out how to automate the width argument. For my standard font (Calibri 9) and all cells set to auto width with no wrapping, 1.9 * selection.width seems to work OK, but that seems clunky. Is there any HTML tag that says to autosize the table width without text wrapping?



To set Font = Calibri 9 maybe this (not exactly what you want because i'm using size = 1, but quite similar)




```

```


Test

Data Range

*A*
*B*
*C*
*D*
*E*
*F*

*1*
Name
Scores
Rank
V/F
Date
Errors

*2*
John
30
4
VERDADEIRO
01/01/2013
#DIV/0!

*3*
Mary
33
3
FALSO
02/01/2013
#N/D

*4*
Mike
34
2
FALSO
03/01/2013
#NÃM!

*5*
William
36
1
VERDADEIRO
04/01/2013
#VALOR!

----------


## shg

> GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")



Curious. In my registry, I see {3D62E9A1-D243-11D2-B561-00A0C92E6848}

----------


## JosephP

that is the igfxcfg.DataObject class rather than the Forms one

----------


## Tony Valko

One last test...


Data Range

*A*
*B*
*C*
*D*
*E*

*1*
Dates
Numbers
Text
Logicals
Errors

*2*
5/15/2004
-0.255
Ya
TRUE
#N/A

*3*
12/26/2004
16
Ba
FALSE
#REF!

*4*
11/7/2001
1E+100
Da

#NUM!

*5*
8/18/2007
2
Ba

#VALUE!

*6*
2/8/2011
8888
Do

#NULL!

*7*
12/25/2005
2.814
I

#NAME?

*8*
6/15/2013
7
C



*9*
1/28/2007
1
U

----------


## protonLeah

What's the finalized version of the code?

----------


## mlcb

> What's the finalized version of the code?



This is the most recent and updated version




```

```


Tony Valko added red color to column letters  and row numbers what is not contemplated in the code above.

M.

----------


## mlcb

Complementing the previous post

*Installation*
Create a module in Personal Workbook and paste the code above
Create a new command (macro) in QAT pointing to Sub CopyRngToHTML

*Usage*
Select the desired range
Click in the new link of QAT
Paste in the forum reply page

*Remarks*
1. If wished, is possible to set the font size after the code is executed to, for example, size = 1 inserting manually the following BB codes in the forum reply page: 

['size=1] without the ' , before the tag ['table] without the '  

and 

[/size] after the tag [/table]


2. Is possible to set a default font size, for example size =2, with a slight modification in Sub RngToHTML in the code line strTable = ....





```

```



3. The same idea is applicable to font type, for example,
[font=Arial Black}
......
...... 
....
[/font].


*Example with Font=Arial Black and size=2*

Data Range

*A*
*B*
*C*
*D*

*1*
Name
Scores
Rank
Date

*2*
John
30
4
01/01/2013

*3*
Mary
33
3
02/01/2013

*4*
Mike
34
2
03/01/2013

*5*
William
36
1
04/01/2013

----------


## Tony Valko

I'm adding a link to post #102 in my signature.

Great job, Marcelo!  :Cool:

----------


## shg

Nice work, Marcelo.

----------


## mlcb

> I'm adding a link to post #102 in my signature.
> 
> Great job, Marcelo!



Tony,

Thank you very much for your kind words, stimulus and help.

Marcelo

----------


## mlcb

> Nice work, Marcelo.



shg,

The same for you. Thank you very much. 

Your interest and support suggesting and providing an easy solution (rcell.Value2) to align the cells according with its values, were fundamental.

Marcelo

----------


## mlcb

Besides Tony and shg,  I have to mention:

Article from where I took the basic code:
http://dailydoseofexcel.com/archives...-from-a-range/

Contributors (Mr. Excel Forum)
Jon Von der Heyden - ideas and stimulus
Rory - important improvements
Peter (Vog) - function to convert column numbers for letters

Thank you all!

Marcelo

----------


## shg

There's a first cut at an add-in posted at https://app.box.com/s/soezox25h3w0q5s4rcyl

See the Add-Ins tab for the menu item.

----------


## FDibbins

testing (with Biffs really great help and instructions)...

Data Range

*A*

*1*
1

*2*
aa

*3*
2

*4*
bb

*5*
3

*6*
cc



awesome, thanks Biff!!!!

----------


## shg

There's an update posted at the same link with a 'Forum tools' tab on the ribbon, compliments of romperstomper/Rory

----------


## mlcb

For who are not using the add-in and still have an interest.

Follows a new version based in the code provided by shg in the add-in.

This new version keeps the original horizontal alignment if any was chosen.  Otherwise follows Excel's default alignment (Text left, Numbers right, Errors and Booleans centered).

Example with column headings and first column (Name) centralized



*A*
*B*
*C*
*D*
*E*

*1*
Name
Scores
Rank
Date
Errors

*2*
John
30
4
01/01/2013
#DIV/0!

*3*
Mary
33
3
02/01/2013
#N/D

*4*
Mike
34
2
03/01/2013
#NÃM!

*5*
William
36
1
04/01/2013
#VALOR!





New code




```

```

----------


## FDibbins

Maybe i dont know what Im talking about (probably, considering my low VBA knowledge), but it looks like you left off the 1st part...



```
Please Login or Register  to view this content.
```



When I try to run the latest code, I get an error message saying "sub or function not defiled"
This part gets hitghlighted in yellow...



```
Please Login or Register  to view this content.
```


and this part seems to be what it doesnt like?



```
Please Login or Register  to view this content.
```

----------


## jeffreybrown

Hi FDibbins,

I did not test the latest posted, but looking at what you posted, you might want to fix this...




```
Please Login or Register  to view this content.
```


Not sure how much "P"s cost these days but they can't be all that expensive  :Wink: 

And for the other part in your post, look in post #101, you need...




```
Please Login or Register  to view this content.
```

----------


## shg

All of the code is available for inspection/modification in the add-in.

----------


## FDibbins

"P"'s are really expensive here so I didnt add it in case it got damaged  (actually, it just got left off on the copy/paste)

As far as the rest of the cose is concerned, I just copy/pasted from here

----------


## jeffreybrown

shg, the add-in is great, thanks for all those who have helped make this work...

----------


## FDibbins

> All of the code is available for inspection/modification in the add-in.



OK stupid question...where is the add-in?

----------


## jeffreybrown

Look at post #108

----------


## Tony Valko

> shg, the add-in is great, thanks for all those who have helped make this work...



+1

Now all we need to do is to get folks to actually use it!  :Wink:

----------


## FDibbins

Thanks Jeff, I cant access sites like that from work though  :Frown:

----------


## :) Sixthsense :)

Nice work and one request from my end.  if possible please keep the addin in a separate thread instead of having it in a long going discussion area..  If possible stick that thread on top because it's going to help the users of this forum.

Just wanted to add one more piece of suggestion from my end whether is is possible to get the background color too...?

----------


## mlcb

> Just wanted to add one more piece of suggestion from my end whether is is possible to get the background color too...?



Hi Sixthsense,

I tried many, many..., ways to set the bgcolor and, as far as i know, it's not possible with the current version of BB Code.

----------


## :) Sixthsense :)

No issues... just wanted to convey it.  Your current version is more than enough for us....

Thanks a lot  :Smilie:

----------


## mlcb

> No issues... just wanted to convey it.  Your current version is more than enough for us....
> 
> Thanks a lot



Thank you very much for your kind words.

----------


## :) Sixthsense :)

You're Welcome  :Smilie: 

One more suggestion it is possible to *ignore the hidden columns*?

Because in the *below table the L:Q is the hidden columns* which is *coming in visible* while converstion...



E
F
G
H
I
J
K
L
M
N
O
P
Q
R

3
y
y

y
y
y







*3*

4
y
y
y

y
y







*2*

5
y
y
y

y








*1*

----------


## :) Sixthsense :)

An update...

When I use *Alt+;* or *Ctrl+G>>Special>>Visible Cells Only* then using the addin is *ignoring the hidden columns* but *unfortunately it is moving the data after the hidden columns to a new table* like the below...


E
F
G
H
I
J
K

3
y
y

y
y
y


4
y
y
y

y
y


5
y
y
y

y



3
*3*

4
*2*

5
*1*

----------


## mlcb

Testing



*A*
*B*
*C*
*D*
*E*
*F*
*G*
*H*
*I*
*J*
*K*
*R*

*1*





21/ago
28/ago
04/set
11/set
18/set
25/set


*2*

Adams
Shawn

New
y

y
y


2

*3*




Return
y
y

y


1

*4*

Adams
Maribeth

New
y
y

y

y
1

*5*




Return

y
y
y


3

----------


## mlcb

Testing with Row 3 = hidden and columns L:Q hidden



*A*
*B*
*C*
*D*
*E*
*F*
*G*
*H*
*I*
*J*
*K*
*R*

*1*





21/ago
28/ago
04/set
11/set
18/set
25/set


*2*

Adams
Shawn

New
y

y
y


2

*4*

Adams
Maribeth

New
y
y

y

y
1

*5*




Return

y
y
y


3

----------


## mlcb

BTW nice formula in 
http://www.excelforum.com/excel-form...her-blank.html

This is my version (but I was late...)  :Smilie: 

R2
=IFERROR(COUNTIF(INDEX($F2:$Q2,LOOKUP(2,1/(($F2:$Q2="")*(COLUMN($A2:$L2)<MAX(($F2:$Q2="Y")*COLUMN($A2:$L2)))=1),COLUMN($A2:$L2))):$Q2,"y"),COUNTIF($F2:$Q2,"y"))

----------


## mlcb

Sixthsense,

Follows a *test version* to deal with rows/columns hidden




```

```

----------


## :) Sixthsense :)

Thanks for it...

You just post yours in the same thread so that we let the OP to pick the one whichever is convenient for him.

I believe your post #128 seems that you have rectified the hidden col/row issue... Is there any modification in the code?

If so then request the same to implement in the Addin....

*Edit:* You posted the revised code already and thanks for it...  :Smilie:

----------


## mlcb

> Thanks for it...
> 
> You just post yours in the same thread so that we let the OP to pick the one whichever is convenient for him.
> 
> I believe your post #128 seems that you have rectified the hidden col/row issue... Is there any modification in the code?
> 
> If so then request the same to implement in the Addin....
> 
> *Edit:* You posted the revised code already and thanks for it...



This is NOT the code of the add-in. I was just testing a new code.

----------


## :) Sixthsense :)

> This is NOT the code of the add-in. I was just testing a new code.



Oh! Ok.. since I have not gone through the code of that addin and assumed that this is the fine tuned code!

----------


## snb

In order to show the code correctly in this thread I had to replace a [ 5 times by a *{*
Please replace the indicated { by [ in your VBA code.

You might use:




```

```





```

```

----------


## Tony Valko

Test code from post #130...

Columns D and E are supposed to be hidden.



*A*
*B*
*C*
*F*
*G*
*H*

*1*
Dates
Numbers

Text
Logicals
Errors

*2*
9/5/2007
-0.00025

Some Here
TRUE
#VALUE!

*3*
5/14/2004
1.00E+100

More



*4*
3/4/2000
1


FALSE
#REF!

*5*
4/6/2007
0

Ice Cream
TRUE

----------


## Tony Valko

Made the row/column headers red...



*A*
*B*
*C*
*F*
*G*
*H*

*1*
Dates
Numbers

Text
Logicals
Errors

*2*
9/5/2007
-0.00025

Some Here
TRUE
#VALUE!

*3*
5/14/2004
1.00E+100

More



*4*
3/4/2000
1


FALSE
#REF!

*5*
4/6/2007
0

Ice Cream
TRUE

----------


## Tony Valko

I think the default font size =2.

In this line of code:




```

```


Is it necessary to set the size to 2?

Here's the result after removing the size tags:



*A*
*B*
*C*
*F*
*G*
*H*

*1*
Dates
Numbers

Text
Logicals
Errors

*2*
9/5/2007
-0.00025

Some Here
TRUE
#VALUE!

*3*
5/14/2004
1.00E+100

More



*4*
3/4/2000
1


FALSE
#REF!

*5*
4/6/2007
0

Ice Cream
TRUE

----------


## mlcb

> In order to show the code correctly in this thread I had to replace a [ 5 times by a *{*
> Please replace the indicated { by [ in your VBA code.



Try HTML tags instead of CODE tags as below




```

```

----------


## mlcb

> I think the default font size =2.
> 
> In this line of code:
> 
> 
> 
> 
> ```
> 
> ...



Tony, 

Not sure, but I think in other forums, or using different browsers, the default is not 2.

I remember many times, especially in Mr Excel Forum, the size being set to 3.

So, just in case, I prefer to put in the code.

M.

----------


## snb

Revised edition:



```

```





```

```

----------


## Tony Valko

OK, that sounds like a good idea!  :Cool:

----------


## FDibbins

OK strange.  

At work, I have win 7 and 2007, and the add-in works fine there
At home, I have win 8 and 2007, and it worked fine here for a while.

Just installed 2010 (still have 2007) and now when I try to run the add-ing, on excel side, it all still seems fine, but when I copy here, all I get is 2 blank lil squares, like this (IF it will show)...


ANyone got any suggestions/ideas?

----------


## :) Sixthsense :)

Whether you installed the addin in both 2007 &  2010 separately?

----------


## FDibbins

OK this is from 2010


A

1
aa

2
bb

3
cc




and this is from 2007...

M

5
95

6
53

7
37

8
20

9
25

10
67

----------


## FDibbins

OK very strange, it was not doing that before, and I had closed 2007 completely, tried again - nothing.  then removed the add-in (even from the personal folder), put it back, and it still wouldnt work  :Confused:

----------


## :) Sixthsense :)

What I suggest is Keep Two Copies of that Addin.

Name 1 copy like For2007 and another copy like For2010.

Seems to be senseless approach but this is what my sense asking me to suggest.

Open your 2007 Excel and Press Alt+T+I (Not in VBA mode) and see whether previously installed addin resides there.  If not then Click Browse button and locate the For2007 addin and give okÂ

Please repeat the above method for 2010 and install the For2010 Addin.

Now check whether this problem still continuesÂ

----------


## romperstomper

I've seen reports of sporadic issues when using the MSForms.DataObject in Win8. I think that's what you're running into.

----------


## FDibbins

@ Six, thanks for the input, I have done that (cant rename the actual macro that appears when you add the addin to the ribbon though)
2007 gives this...

(again grrr)
2010 gives me this...

sooo back where we started from LOL

I think Rory may be right, I dont have this problem on Win 7 with 2007, just at home with Win 8 and 2007/2010

----------


## :) Sixthsense :)

@ FDibbins,

Thanks for testing even though the suggestion is not valid and giving the feedback  :Smilie: 

I am running with XP and not encountered this problem  :Smilie:

----------


## JosephP

does seem like an acknowledged bug: http://social.msdn.microsoft.com/For...d?forum=isvvba
there are a couple links to api code fixes in one of the later posts

----------


## FDibbins

Thanks for the link Joseph, although I would have nooo idea what to do with that.  (1 of the last posts said that the fix didnt work for them, but it's still wayyy above my pay-grade lol)

----------


## JosephP

the only issue there was that the poster didn't know how to alter the code for 64bit Office-it's not that hard ;-)

----------


## FDibbins

flying a plane isnt hard either - if you know how lol

----------


## jaslake

Been trying to figure this out for *AGES*...thanks shg...Post #108


A
B
C
D

1
name

prices
prices

2
A0059

41.5
41.5

3
C0103

14.7
14.7

4
E0530

11.9
11.9

5
E0530

23.3
23.3

6
G7004

14.9
14.9

7
C0103

19.6
19.6

8
C0103

38.7
38.7

9
A0059

30.3
30.3

10
E0530

33.1
33.1

----------


## Tony Valko

Align everything to the right...

Data Range

*A*
*B*
*C*
*D*
*E*

*1*
Dates
Numbers
Text
Logicals
Errors

*2*
5/15/2004
-0.255
Ya
TRUE
#N/A

*3*
12/26/2004
16
Ba
FALSE
#REF!

*4*
11/7/2001
1.00E+100
Da

#NUM!

*5*
8/18/2007
2
Ba

#VALUE!

*6*
2/8/2011
8888
Do

#NULL!

*7*
12/25/2005
2.814
I

#NAME?

*8*
6/15/2013
7
C



*9*
1/28/2007
1
U

----------


## FDibbins

This thread has become so long and involved, it's hard to find where the actual (latest?) code and usage instructions are now  :Frown: 

Any change of starting a new thread with the code/instructions as the very 1st post, so that new-comers (and others) can find it easier?

Also, I mentioned in an earlier post on this thread (no idea where, this already has 155+ posts and 11 pages - hence my request) that with Win8, the code does not always work, sometimes just get 2 small squares instead of what I copied.  I was told it was a W8 problem, wondering if anyone else had the same issue, and if anyone could fix it?

----------


## Tony Valko

> This thread has become so long and involved, it's hard to find where the actual (latest?) code and usage instructions are now 
> 
> Any change of starting a new thread with the code/instructions as the very 1st post, so that new-comers (and others) can find it easier?



I agree.

However, which "latest" code should be the code?

Each "latest" code did something a bit different from the previous "latest" code and not everyone would want to use the last "latest" code. For example, the code I use is slightly different from any of the versions in the thread.

Haven't seen Marcelo on the site in a while.

----------


## mlcb

Hello Friends 

Lately I've been here a few times. 

*This is the code (Personal Workbook) I'm currently using 
*



```
Please Login or Register  to view this content.
```



*There is also the Add-In provided ​​by shg*

*Link*
https://app.box.com/s/soezox25h3w0q5s4rcyl

*Usage*
See the Add-Ins tab for the menu item.


All the best

Marcelo

----------


## romperstomper

Perhaps it just needs a settings form with the most used options in it, so the same add-in will work for most anyone. The code is unlocked anyway I think, so it can always be tweaked if someone has a particular need.

----------


## mlcb

My code in #158 is truncated

New attempt




```

```


M.

----------


## Tony Valko

Posting fake email addresses and having the forum NOT create links:



Data Range

*A*

*1*


*2*
fake1@fakeremailaddress.com

*3*
fake2@fakeremailaddress.com

*4*
fake3@fakeremailaddress.com

*5*
fake4@fakeremailaddress.com

*6*

----------


## Tony Valko

Success!  :Cool:

----------


## klvaughnsd

I realize this is an ancient thread tht probably should not be revived. If so, and I should start a new thread, that will be fine.  I noticed a link in Biff's signature re how to post dta (something to that effect.)

Because I blindly assume Headers argument would be a range, I had error when using this formula. Turned out, function was not running at all.
=RngToBBCode(D2:E7,D1:E1)

Note, if this needs to be deleted, please either explain to me steps necessary, or feel free to delete it (assuming you have the power to do so, like I would assume a Forum Moderator would be able to.)

Okay, I see what I did wrong. I finally figured out that 2nd argument was a boolean while I was trying to use it as a range.  Apparently, if you want headers, you include the line that would be your header in the range you select.  Duh!

Revised formula should be =RngToBBCode(D2:E7,1)
Or true instead of 1. I seem to still be having problems with a Beginning and Ending " but that would be easy enough to ignore or manually delete.
" 

*D*
*E*

*1*
Before
After

*2*
2.5
2.50-

*3*
2.5
2.50-

*4*
5
5.00-

*5*
10
10.00-

*6*
2.5
2.50-

*7*
10
10.00-


"

Note: when I used the code from about 2 posts ago, was having problems with table displaying the words [COLOR} with formatting  and [/COLOR] displaying in the table rather than actually displaying the text in color.

I attempted to remove references to Color in the code and was apparently mostly successful based on my tests.

----------


## Tony Valko

If you're using Excel 2002 as your profile indicates...

Copy the code in post #160.

Paste it into a general module.

Then, to use it...

Let's assume you want to post the range A1:C10 as a table into a forum reply.

Select the range A1:C10
Goto the menu Tools>Macro>Macros
Select the macro name: CopyRngToBBCode
Click the Run button

The macro will generate the bb code and it will be copied to your clipboard.

In Excel Forum, just paste the code into your reply. It might look something like this**:

Data Range

*A*
*B*
*C*

*1*
Header1
Header2
Header3

*2*
Data
Data
Data

*3*
Data
Data
Data

*4*
Data
Data
Data

*5*
Data
Data
Data

*6*
Data
Data
Data

*7*
Data
Data
Data

*8*
Data
Data
Data

*9*
Data
Data
Data

*10*
Data
Data
Data




** the code I use is slightly different as I have modified it to suit my own preferences.

----------


## klvaughnsd

I did use code from post 160, but was using the UDF. Let's see what happens with macro:

 

*D*
*E*

*1*
Before
After

*2*
2.5
2.50-

*3*
2.5
2.50-

*4*
5
5.00-

*5*
10
10.00-

*6*
2.5
2.50-

*7*
10
10.00-




Cool. That  looks nice. Thanks very much!

----------


## Tony Valko

Looks good!

Thanks for the feedback!  :Cool:

----------


## snb

Made my own variant:
If the selection is 1 cell it will be expanded to the currentregion, otherwise the selection will be converted into a HTML table.




```
Please Login or Register  to view this content.
```


The function can't be posted here because it's been evaluated as html.

----------


## klvaughnsd

> Made my own variant:
> If the selection is 1 cell it will be expanded to the currentregion, otherwise the selection will be converted into a HTML table.
> 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> ...




This gives me a small excuse to ask about this line:
With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

I don't need to fully understand it, but would appreciate at least a short explanation of what's going on with this line of code.

I apologize if it's already been explained in this thread. I read the first couple pages of this thread, then skipped to the (previously) last page of the thread

----------


## snb

see also #160

it's creating a new instance of the dataobject, based on the reference Msforms 2.0

----------


## klvaughnsd

> see also #160
> 
> it's creating a new instance of the dataobject, based on the reference Msforms 2.0



Yes, I got it from post #160 which is where I first became curious as to what it meant. Cool. Thanks for the info. The macro containing it works nice.

----------


## Tony Valko

Testing font color...

Data Range

*A*
*B*

*1*
1
8

*2*
2
2

*3*
8
7

*4*
3
3

*5*
8
5

*6*
8
4

*7*
4
4

*8*
5
6

*9*
8
9

*10*
6
5

----------


## Tony Valko

Testing font color...

Data Range

*A*
*B*

*1*
1
8

*2*
2
2

*3*
*8*
*7*

*4*
3
3

*5*
8
5

*6*
*8*
*4*

*7*
4
4

*8*
5
6

*9*
*8*
*9*

*10*
6
5

----------


## Tony Valko

One last test of font color...

Data Range

*A*
*B*

*1*
1
8

*2*
2
2

*3*
8
7

*4*
3
3

*5*
8
5

*6*
8
4

*7*
4
4

*8*
5
6

*9*
8
9

*10*
6
5

----------


## Tony Valko

Testing font color...

Data Range

*A*
*B*

*1*
61
15

*2*
*78*
67

*3*
1
36

*4*
94
72

*5*
*22*
*49*

*6*
91
63

*7*
*69*
35

*8*
27
71

*9*
46
63

*10*
*60*
30

----------


## FDibbins

Hay Tony, was that the last after-last last test?   :Wink:

----------


## Tony Valko

There might be more!

I'm trying to come up with the best color combinations where the row/column headers standout and I can highlight specific pieces of the data so they also standout.

----------


## FDibbins

I know I have said this before in this thread (somwhere in the 12 pages???), but what is the latest itteration of this now, and where/how can it be found?  

This really really helps a LOT when posting answers, and it would be great to keep it updated with the latest version  :Smilie: 

Also, when running this (the version I have) on W8 and 2007 or 2010, sometimes it just pastes 2 small squares instead of the table - any fix for that?

----------


## Tony Valko

Without going through the entire thread (again) I can't tell you which is the latest update.

I can post the code I use if you want me to.

As for the problem you mentioned...I've not experienced anything like that but I use Win7.

----------


## shg

The version I use is at https://app.box.com/s/soezox25h3w0q5s4rcyl

----------


## FDibbins

Yup, I only have that on W8, not W7 (both running 2007)

I started at post # 142 on page 10

----------


## romperstomper

@Ford,

I have a version here which doesn't use the DataObject (it uses API calls) and so shouldn't suffer from the 'two squares' issue on Win 8. It also has tools for posting using HTML which aren't much use here, but you can always simply take the clipboard code and use it in Marcelo's add-in instead of the DataObject code.

----------


## FlameRetired

A test. Pray for me.




```
Please Login or Register  to view this content.
```


OK; that works...sort of. Cannot find those BB code tags. 
Help?

----------


## FlameRetired

New test:


A
B
C
D

1
Jupiter

Wyoming
-16

2
Saturn

Alabama
20

3
Uranus

Alaska
-10

4
Neptune

Arizona
-8

5
Pluto

Arkansas
15

6
Mercury

California
15

7
Venus

Colorado
5

8
Earth

Connecticut
-14

9
Mars

Delaware
4

10
Jupiter

Florida
6

11
Saturn

Georgia
-5

12
Uranus

Hawaii
-7




That worked except for the colors. I keep trying.

----------


## FlameRetired

One more time:


A
B
C
D

1
Jupiter

Wyoming
-16

2
Saturn

Alabama
20

3
Uranus

Alaska
-10

4
Neptune

Arizona
-8

5
Pluto

Arkansas
15

6
Mercury

California
15

7
Venus

Colorado
5

8
Earth

Connecticut
-14

9
Mars

Delaware
4

10
Jupiter

Florida
6

11
Saturn

Georgia
-5

12
Uranus

Hawaii
-7




That's great!! Thank you Marcelo and all. I will use this.
FR

----------


## FlameRetired

'Nother test...... 

*E*
*F*
*G*
*H*
*I*
*J*

*9*







*10*

Customer
Product Qty




*11*


Corks
Bottles
Barrels


*12*

Contoso, Ltd.
14
9
3


*13*

Coho Winery
2
11
15


*14*







*15*







*16*

Price
Weight (lbs)
Product



*17*

$200 
4
Corks (500/box)



*18*

$250 
42
Bottles (case)



*19*

$425 
115
Barrel



*20*

MMULT





*21*

Customer
Sales
Total weight



*22*

Contoso, Ltd.
$6,325 
779
#N/A


*23*

Coho Winery
$9,525 
2195
#N/A


*24*


#N/A
#N/A
#N/A


*25*

----------


## FDibbins

You testing the tests?  lol

@ Rory, thanks for that, I had not noticed your post before.  I an on W7 right now, will take a look when I get home (W8)

----------


## FDibbins

deleted duplicate post

----------


## shg

Version 0.5 adds options to suppress the header and grid:

*Statement*
*Statement*
*Statement*
*Statement*
*Remark*
*Marker*
*Operator*

Call
Else
Loop
Sub
Rem
Any
AddressOf

Case
ElseIf
LSet
Type

As
And

Close
End
Next
Unlock

ByRef
Eqv

Const
EndIf
On
Wend

ByVal
Imp

Â
Â
Â
Â

Â
Â




The add-in is at https://app.box.com/s/soezox25h3w0q5s4rcyl

----------


## shg

Is anyone having problems with the add-in in Excel 2013?

----------


## teylyn

I think the issue is Windows 8, not XL 2013, as cropped up in posts 142, 147 and with the version posted by romperstomper in post 181. That one works fine in Win 8.

----------


## FDibbins

to confirm, I have used Rompers suggestion from post 181 in Win8 and (now) win 8.1 at home, and it does work great, still using win 7 at work here though

My apologies Rory, I thought I had thanked you for this before, but looking back, apparently I did not - so thank you bunches for that  :Smilie:

----------


## romperstomper

You're welcome, Ford.  :Smilie:

----------


## shg

I think that is an apalling bug.

----------


## romperstomper

What would you expect from an appalling OS?  :Wink:

----------


## :) Sixthsense :)

> I have a version here



Hi Rory,

I am using your addin for posting the table for OP's and it's working fine and thanks for it  :Smilie: 

One small missing piece is that it's showing the Normal formula and Array Formula in the same way... without covering the formula with curly braces {} for array formulas...

Here is the thread as an example  :Smilie: 

http://www.excelforum.com/excel-form...-of-pairs.html

If you implement it then it will be very much useful to me and OP as well  :Wink:

----------


## romperstomper

I'll see what I can do when I have a moment, Sixth.  :Smilie:

----------


## romperstomper

OK, should be done now and I have updated the version on my site.

----------


## :) Sixthsense :)

> OK, should be done now and I have updated the version on my site.



Thanks for implementing the requested feature  :Smilie: 

I will give a try and let you know  :Wink:

----------


## romperstomper

Just as an FYI, I have created a Mac-friendly version of the BB Code add-in. You can get it at the bottom of the page here: http://excelmatters.com/excel-forums/

----------


## FDibbins

> You're welcome, Ford.



I have noticed with what I got from your post 181, that sometimes, it seems to remember the file it was last used in (if I have a few different files open), then when I try and use it again, it jumps back to that file, and wont accept the range from the current file.  I end up having to close the file I used it previously in, and then it works fine again.

Also, instead of actually showing colors, it shows their names instead (I think its only background colors, not font colors...

B
C
D
E
F

6
[BGCOLOR=#FFFF00]1[/BGCOLOR]
[BGCOLOR=#FFFF00]2[/BGCOLOR]
[BGCOLOR=#FFFF00]3[/BGCOLOR]
[BGCOLOR=#FFFF00]4[/BGCOLOR]
[BGCOLOR=#FFFF00]5[/BGCOLOR]

7
[BGCOLOR=#FFFF00]102[/BGCOLOR]
[BGCOLOR=#FFFF00]202[/BGCOLOR]
[BGCOLOR=#FFFF00]302[/BGCOLOR]
[BGCOLOR=#FFFF00]402[/BGCOLOR]
[BGCOLOR=#FFFF00]#N/A[/BGCOLOR]

8
[BGCOLOR=#FFFF00]104[/BGCOLOR]
[BGCOLOR=#FFFF00]204[/BGCOLOR]
[BGCOLOR=#FFFF00]304[/BGCOLOR]
[BGCOLOR=#FFFF00]404[/BGCOLOR]
[BGCOLOR=#FFFF00]#N/A[/BGCOLOR]

9
[BGCOLOR=#FFFF00]106[/BGCOLOR]
[BGCOLOR=#FFFF00]206[/BGCOLOR]
[BGCOLOR=#FFFF00]306[/BGCOLOR]
[BGCOLOR=#FFFF00]406[/BGCOLOR]
[BGCOLOR=#FFFF00]#N/A[/BGCOLOR]

10
[BGCOLOR=#FFFF00]108[/BGCOLOR]
[BGCOLOR=#FFFF00]208[/BGCOLOR]
[BGCOLOR=#FFFF00]308[/BGCOLOR]
[BGCOLOR=#FFFF00]408[/BGCOLOR]
[BGCOLOR=#FFFF00]#N/A[/BGCOLOR]

11
[BGCOLOR=#FFFF00]114[/BGCOLOR]
[BGCOLOR=#FFFF00]214[/BGCOLOR]
[BGCOLOR=#FFFF00]314[/BGCOLOR]
[BGCOLOR=#FFFF00]414[/BGCOLOR]
[BGCOLOR=#FFFF00]#N/A[/BGCOLOR]

12
[BGCOLOR=#FFFF00]116[/BGCOLOR]
[BGCOLOR=#FFFF00]216[/BGCOLOR]
[BGCOLOR=#FFFF00]316[/BGCOLOR]
[BGCOLOR=#FFFF00]416[/BGCOLOR]
[BGCOLOR=#FFFF00]#N/A[/BGCOLOR]

13
[BGCOLOR=#FFFF00][/BGCOLOR]
[BGCOLOR=#FFFF00][/BGCOLOR]
[BGCOLOR=#FFFF00][/BGCOLOR]
[BGCOLOR=#FFFF00][/BGCOLOR]
[BGCOLOR=#FFFF00][/BGCOLOR]




Otherwise, this thing works great!!

----------


## romperstomper

Hmm, looks like I needed more testing before trying to consolidate the HTML code with the BBCode version!

----------


## FDibbins

haha.  If its any help, Im running 8.1 (doesnt say what build), and Office 13 on I7 with 12 gig ram  (not sure if this happens in office 10, but I can testy if needed)

Its not a huge big deal, I just remove all colors before using.  The "switch back to last copy area" can be a pain

----------


## FlameRetired

@ Ford





> I have noticed with what I got from your post 181, that sometimes, it seems to remember the file it was last used in (if I have a few different files open), then when I try and use it again, it jumps back to that file, and wont accept the range from the current file. I end up having to close the file I used it previously in, and then it works fine again.



Me, too. I've discovered that if I go into the VBA editor and hit reset all is well.

----------


## josafe88

Anybody here has an idea how will I can copy and paste the Alphanumeric beside the numbers column? When I copy and paste the alphanumeric it cannot be pasted because the area are not the same size and shape. I want the format of the numbers should be the same with the alphanumeric. Your help is greatly appreciated guys. Thank you so much. 

help.jpg

----------


## Fotis1991

*josafe88* 

Welcome to the forum. Pls take some minutes to read forum rules before you post again, because:

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## romperstomper

@Ford,
I've loaded an updated version. Can you download and retest? I think it fixes both issues. (there is no BBCode for cell fill, so it simply ignores that)

----------


## FDibbins

Will do Rory.  I am at work right now (W7/2007), so I will take a look when I get home - thats where it happens
(now, all I have to do is find te post that has the upload LOL)

----------


## FDibbins

> @ Ford
> 
> Me, too. I've discovered that if I go into the VBA editor and hit reset all is well.



Thanks I will try that.  Was that just for the 'switch back" or for the color thing too?

----------


## romperstomper

> (now, all I have to do is find te post that has the upload LOL)



You can download my current version from the link at the bottom of the page here: http://excelmatters.com/excel-forums/

----------


## FDibbins

Thanks for the pointer, Rory.  I found the link, will try it when I get home (I work 3-11)

----------


## FDibbins

Romper, I have tried that link again, but it doesnt seem to be working?

----------


## teylyn

I think Rory has had it with this forum. 

I can't quite tell what link you are referring to, but the link in Rory's post #209 takes me to his blog just fine. On that page scroll to the end of his text and find the link to the tool.

For convenience's sake, here it is again:

https://www.dropbox.com/s/31r9s6t9j6...ools.xlam?dl=0

cheers, teylyn

----------


## FDibbins

teylyn, thanks for that.  I could get to his site
http://excelmatters.com/excel-forums/
but the link to the file (marked "here") did not work - but when I just checked again now, it does

Again, thank you  :Smilie:

----------


## teylyn

You're welcome. It's  great tool and worth spreading.

----------


## Doc.AElstein

> You're welcome. It's  great tool and worth spreading.



Here, here
Rory’s gone…… 
_ ……  but his Tool Lives on

_. May it continue to come up frequently for us.

Alan

P.s. …..


Part of my My earlier Signature, (  currently suspended in favor of an important message )

Post screenshots of your Spreadsheet Ranges that can be copied into a Spreadsheet
-See bottom of the page (Win & Mac): http://excelmatters.com/excel-forums/
https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv

And a few other  links to versions of the Add-In

https://app.box.com/files/0/f/366413.../f_27863000440  ( XL 2007 + )
https://app.box.com/files/0/f/366413.../f_32131854803  (XL 2010 + )

Simple BB Borders code
https://app.box.com/s/soezox25h3w0q5s4rcyl ( XL 2010 + )

Simple Red BB Borders codes
https://app.box.com/files/0/f/366413.../f_32560517515   (XL 2010 + )
https://app.box.com/files/0/f/366413.../f_32629351849    (XL 2010 + )

*Note:*
When trying different Forum Tool ADD-Ins, always delete all versions form your computer entirely  except the one you are trying. ( Otherwise Excel seem to decide which Add-In it uses regardless of the one you specify!!!! )


http://www.mrexcel.com/forum/about-b...rder-help.html

----------


## JeteMc

I uploaded the BB code earlier this morning then attempted to use it a little later when posting a solution. When I selected copy Excel quit responding and went into a recovery mode. When it started again the Forum Tools tab was gone, much the same as what had happened some time ago with PowerPivot 2010. I have an older laptop so maybe it is just a memory issue in which case there is probably nothing that can be done. I just wondered if anyone happens to have any ideas?

----------


## Doc.AElstein

Hi JeteMc




> ...uploaded the BB code earlier this morning..... selected copy Excel quit responding and went into a recovery mode. When it started again the Forum Tools tab was gone,..... I just wondered if anyone happens to have any ideas?



I have older computers and Excel versions... This crashing thing on running a program is happening a lot more often with me recently
http://www.mrexcel.com/forum/general...l-crashes.html
, but not specifically as a result of an Add-in attempt. Of course you realise that when the File recovers itself it it will either open your last saved File or , if it can, a more recent one it did automatically. So if you did not save the File, ( and i think importantly also you may need to save the Add-in when you have it selected in the VB Editor window, ) then it may be gone when Excel recovers. 

Maybe some ideas then:
*_1)*    As I mentioned in the above Thread, this strange crashing problem seems to be less apparent if I run a code in F8 once or twice the first times. If you are using the Rory Add-ins then that means 
open Module
 mBBCode 
in the VB Editor, ( Alt 11 ) 

Put a stop beside
Sub CopyRngToBBC(rInp As Range, bFrm As Boolean, bA1 As Boolean, bColour As Boolean)

then run 
Sub ShowRangeToBBCForm()
in debug mode. ( F8 )

    That may help, or at least in the debug mode you may see the  error point where Excel is freaking out for you

_2) You will find it a bit difficult to contact the author of these codes here. You may want to ask here:
http://excelmatters.com/excel-forums/

_3 ) If you are still having the problems with the Add-in for a BB Code generation, then maybe a simple "stand alone" Code could help get you by in the mean time. Check out these Posts for info and a File with some codes in.
http://www.excelforum.com/developmen...ml#post4293889
http://www.excelfox.com/forum/f17/te...2079/#post9635
http://www.excelfox.com/forum/f13/bb...2077/#post9637

and here the File again
https://app.box.com/s/zhz7awdag4nl1zs6564s9zzcwp50e4w9
That one I keep up to date..

The current one i also put in the attachment to this thread

Hope that maybe of some help

Alan

----------


## Tony Valko

Anyone know how to do this?

Data Range

*A*

*1*
10

*2*
20

*3*
30

*4*
40

*5*
50

*6*
------




This is the code generated for my table:





```

```


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.

----------


## FlameRetired

Testing.

*Edit* Do you mean like this? Using RangeToBBCode V0.*7*.xlam

Don't know which code change does the trick.

Does this help?

Row\Col
A

1
10

2
20

3
30

4
40

5
50




*More edit*




```

```

----------


## Tony Valko

```

```


That looks like what I need.

Let me see what happens when I try it.

----------


## Doc.AElstein

Hi Tony




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



Try out the last "Wonk" here ( At end of this Post: )
http://www.excelforum.com/the-water-...ml#post4324451

Alan

----------


## Tony Valko

Data Range

*A*

*1*
10

*2*
20

*3*
30

*4*
40

*5*
50

*6*
------

----------


## Tony Valko

Looks good!  :Cool: 

Thanks!

----------


## FlameRetired

Great. __________ :Smilie: 

Thanks for the rep.

----------


## Tony Valko

> Try out the last "Wonk" here ( At end of this Post: )



Yes, that also helped.

Thanks!  :Cool:

----------


## Doc.AElstein

Hi Tony




> Yes, that also helped.
> Thanks!



Yous welcome, tzhanks for Rep Thiungy
Wonk Wonk  :Wink:

----------


## mlcb

> 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




```

```


Greetings

M.

----------


## FlameRetired

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

----------


## Tony Valko

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!  :Cool:

----------


## Doc.AElstein

@Dave





> ......
> 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.   :Confused:     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” :Smilie: 
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    :Smilie:    )

Alan

----------


## FlameRetired

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  :Smilie: 

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

Thanks again,
Dave

----------


## Doc.AElstein

Hi Dave




> .... 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.

----------


## mlcb

*Using the code in two workbooks*

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





```

```



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

----------


## FlameRetired

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

----------


## FlameRetired

@ Doc.AElstein

Thank you, again.

Maybe Marcelo will enlighten us both.

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

Dave

----------


## mlcb

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

----------


## mlcb

Ignore this post

----------


## FDibbins

> 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)

----------


## FDibbins

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?

----------


## Doc.AElstein

> ......
> 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*

----------


## Doc.AElstein

*Hi Dave*
*_1) First Just some* extra quick info in addition to that from Marcelo  on VBA Learning:
_a) These Threads has some good you Tube play lists... video series etc...
http://www.excelforum.com/excel-gene...ml#post4272300
http://www.eileenslounge.com/viewtop...175146#p175146
_b) the list of learning material that hiker95 often makes is a bit big.. but he tries to include the best he knows
http://www.mrexcel.com/forum/excel-q...ml#post4379195
http://www.mrexcel.com/forum/excel-q...ml#post4379209
_.................................................................................


*_2 ).........*
_..........................




> .......
> 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.......
> I think I need a "seeing-eye-programmer"



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

Marcelo will probably confirm , but i think, he just put back in the line
Application.CutCopyMode = False
_ .. that is to say he edited just that line to take out the took the ' ( -  you probably know  a '   has the effect of making everything after it on that line turn into comments, that is to say  just notes for your convenience, which VBA just ignores. So rather than removing a line, often one “comments them out” by putting a ' before. That way you can put the line back in later if you then change your mind. Or alternatively it just serves as a  bit of documentation as a reminder for you as to what you did. )

It is always difficult to help not knowing exactly what level you are.* I try:
*
Basically we are talking about two different things. *A) Add-Ins* and *B) what i call “stand alone codes”*

*_A)     Add-Ins*

*Add-Ins* are more “user friendly” things,  ( at least once you have them installed !! installing can be very user unfriendly  lol  !! )). Once there you have something extra up in the Ribbon bar and you click on it and then a Message box prompts you etc.. etc... etc.......
If you go for the “Add-In option”,  then Probably Rory’s is the best to go for. I think He has complied together , added to , improved etc..   codes from the likes of  Marcelo Branco,   Tony Valko,  Jon von der Heyden, Peter (VoG), and  Stephen Gersuk (  and i apologise if i have missed anyone. )
Current Add-in and some ( badly) written notes from me on how to install an Add-In for example here:

*Add-In*:
https://www.dropbox.com/s/31r9s6t9j6...ools.xlam?dl=0

*Notes:*
http://www.excelforum.com/the-water-...ml#post4109080
http://www.excelforum.com/developmen...ly-needed.html
https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv

(-..also  ...    Jon von der Heyden did some notes on a specific HTML Code Generator Add-In that is not supported  here at Excel Forum, but again it may give you an idea how to install an Add-in
http://www.mrexcel.com/forum/about-b...ml#post2507729
_.... )
_...............................................................


*_B ) “Stand Alone Codes”* 

An example of this is what Marcelo presented yesterday. That is one of a few codes that would probably be incorporated into one of those Add-Ins.

Again i do not know your exact level of VBBA knowledge. 
This is the typical gem given to beginners.... 

_How to get a macro up and running:

(IMPORTANT: always save your File just before you run any macro. –This is Because you cannot use the Excel backward thing to clear any changes made by a macro. 
(Actually When you “work on your File” you effectively work on a copy of your File. It only becomes your file when you save http://www.mrexcel.com/forum/excel-q...ml#post4425428 ...._) 
_ - So If anything goes wrong after running a macro, then simply close the file without saving, then open it  and no damage will have been done to your original File. )

To Install a macro:
1. Copy the code given to you to the clipboard. ( Highlight code and Hit Ctrl C )
2. Open a NEW workbook ( or the workbook you want the code in )
3. Press the keys ALT + F11 to open the Visual Basic Editor
At this point you should see the cursor flashing in a big empty window 
 ( If not then select from the VB Editor ribbon  ----  Insert ------   module )
4. Where the cursor is flashing, paste the code
5. Do any editing of the code you wish to at this stage.
6. Press the keys ALT + F11 to return to Excel ( or press ALT and q simultaneously to close the VB Editor )

To run or wonk  or work   on a macro:
7. When in Excel, press ALT + F8 to display the Run Macro Dialog Window Box Thing. 
8. You can choose to run the code  ( alternatively Double Click the macro's name to Run it.)
Or
9. You can choose to work ( or wonk** ) on it. – This option is good to find the macro if you have many and are not sure where they all are )
_
( **a wonk is what i do on the Pros codes..  in other words i ruin them for my own perverse pleasure  lol    )
_................................

You may want as an alternative to putting the code in a particular File,  to create a PERSONAL.XLSB, and ADD a button to ribbon ( XL 2010 + ) or ADD a quick access symbol XL 2007 +. 
( That way it is similarly to the Add-In "always there " for you when you open Excel )

For that i cannot explain it better than the Link Marcelo gave you
http://www.rondebruin.nl/win/personal.htm
Or you may find mine and Jerry’s instructions a bit quicker to follow:
http://www.excelforum.com/tips-and-t...ml#post4109262
http://www.excelforum.com/the-water-...ml#post4110298

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

Some other “_Alan options”_!!!!

_ C) I “collect” all these codes in this file
https://app.box.com/s/zhz7awdag4nl1zs6564s9zzcwp50e4w9
So just play with that, running for example this code ( Found from  Excel  as in the above instructions 7.  above )
Sub ShowRangeToBBCFormJune()

( I assume  that you know that the start point before running any BB Code Generating  Add-In or  “stand alone code”   is to highlight the range you want to show in a Forum Post. That Range is then converted to a BB Code to produce a Table and that code placed in the Clipboard.  You then paste in the Forum Editor ..
_ – Practice pasting in here
http://www.excelforum.com/development-testing-forum/
And here:
http://www.mrexcel.com/forum/test-here/

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

_D) Last just for Fun .  For you and Ford as you were over there more recently, ( where the Post editor is kinder and allows a lot at once to be pasted:......
http://www.mrexcel.com/forum/test-he...ml#post4480787
( but note, that Thread will likely be routinely deleted in a few days )



*Alan*

----------


## Tony Valko

Test new code...

 

*A*
*B*
*C*

*1*
Type
Amount
Y/N

*4*
Data3
82
N

*5*
Data4
84
N




Range has autofilter applied

----------


## FlameRetired

Just testing

 

*A*
*B*

*1*
E
FALSE

*2*
H


*3*
H
FALSE

*4*
K


*5*
A
FALSE

*6*
Y


*7*
E
TRUE

*8*
E


*9*
U
FALSE

*10*
J


*11*
G
FALSE

*12*
T


*13*
H
FALSE

*14*
A


*15*
Y
FALSE

*16*
V


*17*
B
FALSE

*18*
Q


*19*
A
FALSE

*20*
B


*21*
I
FALSE

*22*
O


*23*
O
FALSE

*24*
U


*25*
T
TRUE

*26*
T

----------

