$$Excel-Macros$$ Re-creating a userform

2011-04-28 Thread JYH 
I am trying to create a UserForm X. If, for whatever reason, form X 
already exist, I want to remove it and re-create it again. 

Sounds simple, right? 
Here's the code: 

Dim TempForm As Object ' VBComponent 
On Error Resume Next 
Set TempForm = ThisWorkbook.VBProject.VBComponents("Bob") 
ThisWorkbook.VBProject.VBComponents.Remove TempForm 
On Error GoTo 0 
'Set TempForm = Nothing 
Set TempForm = 
ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm) 
ThisWorkbook.VBProject.VBComponents(TempForm.name).name = "Bob" 

Last line gives me a "Run time error 75: path file access error"... 

Any suggestion? 

And, before you ask, even god have less privilege than me on that 
machine... 

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Re: Know about VBA Programming

2011-04-28 Thread JYH 
Two essentials:
http://www.cpearson.com/Excel/MainPage.aspx
http://www.ozgrid.com/
 
If you have basic Programing knowledge, these two sites should have nearly 
everything you need. If you don't, get a basic cours in programing first.

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Re:Data - text to date format

2011-04-17 Thread JYH 
That solution, although not entirely wrong, would not work on all
computers.

This is mainly caused because there are many potential problems :
1) the date items separator (here ".")
2) the date structure of the text field (here it is "ddmm")
3) the date structure as used by your system (could be mmddyy, yymmdd,
ddmmyy, mmdd, etc.)

If you know for sure the structure of the text value provided to you
(in this case, "dd.mm."), I always suggest you make sure you build
the right excel internal date structure like that:
=DATE(RIGHT(B3;4);MID(B3;4;2);LEFT(B3;2))-
DATE(RIGHT(A3;4);MID(A3;4;2);LEFT(A3;2))


On 17 avr, 10:21, ashish koul  wrote:
> try this
>
> =SUBSTITUTE(B3,".","-")-SUBSTITUTE(A3,".","-")
>
> On Sun, Apr 17, 2011 at 5:41 PM, Mohd Sanusi Mohd Noor
> wrote:
>
>
>
>
>
>
>
>
>
>
>
> > Dear all,
>
> > Would like to seek your kind assistance/help on the date format.
> > I've a data on date derive from SAP, when transfer to excel the date is in
> > text format.
> > Is there any way/method to change the dates from text to date format.
>
> > Attached is the sample data for reference.
>
> > The assistance on the above is highly appreciated.
>
> > Tq
>
> > Sanusi
>
> > --
>
> > --
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/discussexcel
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com 
> *akoul*.wordpress.com 
> My Linkedin Profile 
>
> P Before printing, think about the environment.

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ How to copy ThisWorkbook events code into another "thisworkbook module" on another workbook

2010-09-23 Thread JYH 
Good day everyone,

I have a small fleet of Excel apps who, sometime, need updating.
For modules, I simply
ThisWorkbook.VBProject.VBComponents("WhateverModule").Export
tempFile
DestWbk.VBProject.VBComponents.Import tempFile
Kill (tempFile)


But I can not use the same code to import into ThisWorkbook as I do
for modules.
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Export
tempFile
DestWbk.VBProject.VBComponents.Import tempFile
Kill (tempFile)


Instead of replacing (or adding) the code within ThisWorkbook, it
creates a new Class Modules named "ThisWorkbook1".


Do you have any suggestion on how could I fix that?


The only other solution is to manually set all my events to something
static refering to a module.


Thank you

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 7000 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

$$Excel-Macros$$ How to copy ThisWorkbook events code into another "thisworkbook module" on another workbook

2010-09-23 Thread JYH 
Good day everyone,

I have a small fleet of Excel apps who, sometime, need updating.
For modules, I simply
ThisWorkbook.VBProject.VBComponents("WhateverModule").Export
tempFile
DestWbk.VBProject.VBComponents.Import tempFile
Kill (tempFile)


But I can not use the same code to import into ThisWorkbook as I do
for modules.
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Export
tempFile
DestWbk.VBProject.VBComponents.Import tempFile
Kill (tempFile)


Instead of replacing (or adding) the code within ThisWorkbook, it
creates a new Class Modules named "ThisWorkbook1".


Do you have any suggestion on how could I fix that?


The only other solution is to manually set all my events to something
static refering to a module.


Thank you

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 7000 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe