VBA Macro help

[versa005]

Hi,

I am new to VBA and I am writing a macro to generate grocery list. I have attached the same as a zip file. (Please extract the file and open the same.)

Steps to follow to generate the list are
1) Open the excel with macro enabled
2) Enter the quantity and brand requrired for that month. Example: If i want gillete shaving foam, i have to enter next to Shaving foam cell, Gillete in brand , qty 1
3) after entering all items, press generate list button
4) the list is generated in word document
5) clearlist will clear the brands and quantity

Method:
1) For generating the list, in VBA, i read all the qty columns in the excel, if it is zero, i pick the brand and item name
2) with this, i form a temporary table in excel and define it as a range
3) the range is then selected and copied into a word document

Question:

1. I am able to open the macro with ALT F11. But i dont want other to view my macro. How this to be done?
2. Also while performing final copy, i am unable to make the table AUTOFIT TO CONTENTS in WORD. I used the attribute


With objWordDoc.tables
TablesAutofit = AUTOFIT_CONTENTS

End With



Please help... thanks a lot

[versa005]

Hi, someone please help...

[fumei]

"1. I am able to open the macro with ALT F11. But i dont want other to view my macro. How this to be done?"

Why do you care? No, it can not be realistically done. Microsoft files are not, and never have been, secure. There are ways to start hiding things, but if someone really wants to see your code, they can.

Dim I, J, X, Y, Z As IntegerDo you know that only Z is declared as an Integer? The others are Variants.

What made you think

TablesAutofit = AUTOFIT_CONTENTS

was correct? Try recording a macro in Word, doing an AutoFit, and then look at the code.


.AutoFitBehavior (wdAutoFitContent)



'Delete "Empty" Tables, the ones that have only 1 row (the header row)
For Each wtable In objWordDoc.tables
wtable.Select
wtable.Delete
Next
"have only 1 row" - no, it does not do ANYTHING regarding tables with one row. The code above deletes every table in the document.

[fumei]

"1. I am able to open the macro with ALT F11. But i dont want other to view my macro. How this to be done?"

Why do you care? No, it can not be realistically done. Microsoft files are not, and never have been, secure. There are ways to start hiding things, but if someone really wants to see your code, they can.

Dim I, J, X, Y, Z As IntegerDo you know that only Z is declared as an Integer? The others are Variants.

What made you think

TablesAutofit = AUTOFIT_CONTENTS

was correct? Try recording a macro in Word, doing an AutoFit, and then look at the code.


.AutoFitBehavior (wdAutoFitContent)



'Delete "Empty" Tables, the ones that have only 1 row (the header row)
For Each wtable In objWordDoc.tables
wtable.Select
wtable.Delete
Next
"have only 1 row" - no, it does not do ANYTHING regarding tables with one row. The code above deletes every table in the document.

[versa005]

hi gerry, thanks for your help.

as of now, i have the script GENERATE LIST in excel. my required output is an autofitted table in word.

is there any way to execute AutoFitBehavior (wdAutoFitContent) in word from excel macro. kindly help

[macropod]

is there any way to execute AutoFitBehavior (wdAutoFitContent) in word from excel macro. kindly helpAssuming you're calling your Word table correctly and you're using:
. early binding, you can use AutoFitBehavior (wdAutoFitContent);
. late binding, you can use AutoFitBehavior (1).