About

VBA4ALL has got a new home @ VBA4ALL.COM

8 thoughts on “About”

  1. Man your script to extract images from excel file and then name them according to a specified cell was a savior. I really would like to thank you and appreciate your effort.
    Many Thanks I owe you a drink some day…

  2. good works!

  3. bonCodigo said:

    Great blog showing up hidden features of VBA. I am in the field. Doing any Gits? How to get in touch with you?

  4. I like your insight into VBA. I started about 1 1/2 years ago. I have a challenge for you. Is there a way to prevent the resizing of ActiveX objects after a click event occurs when connected to a projector or another device. Excel automatically adjusts the sizes (usualy increases them) and I can’t think of anyway to prevent this other than resizing it through VBA everytime one of these buttons are clicked. From my understanding ActiveX objects are automatically adjusted based on the screen resolution of your monitor. This problem has plagued me from the beginning. Any thoughts?

    • Hi Neil.

      This sounds like the well known activeX bug in Excel. There isn’t really a “fix” for it even though I have seen some solutions involving resizing of controls upon each action taken on spreadsheets ( ie. using Worksheet_Change() event I believe ). You may have a look at THIS QUESTION on StackOverflow and check out the RELATED column on the right hand side for alternative solutions. Also, check out the properties of the ActiveX control AFAIK there is a property responsible for the resizing.

      Also, (I havent tested it myself yet) but one alternative would be THIS Q&A which I believe allows you to adjust the sizes based on the screen properties if you tweak it a bit. Hope this helps.

      • Thanks for the quick response. I’ll check out your links and let you know. I give presentations a lot on the programs I write and I always have to excuse the ActiveX bug. It’s annoying but I live with it knowing it only happens during demonstrations. I have had limited success resizing each control upon clicking on it.

  5. I have another question for you. This involves seperate instances of Excel applications being open. We use SAP and everytime we do an extraction to a spreadsheet it opens up another instance of the Excel application. I have yet to figure out a way to get any macro to communicate with that other instance of Excel that is open. I tried the application.windows function with no success. Granted all I need to do is close the window and re-open the spreadsheet, but I was curious if there is a way to work with the data in the other window.

    To better picture it if you don’t know what I mean is imagine you have Excel open that has several worksheets open on it. You can clilck on each spreadsheet, do a split view, etc. Now imagine a seperate window with other Excel worksheets on it. You can put the windows next to each other and look at each spreadsheet but you can’t link them by macro’s or formulas. Any ideas?

    By the way you need to write a book on VBA. The books out there for beginners are horibble and don’t explain technique very well, they only show the hard coding of VBA. You explain things very well, I admire that.

    Thanks

    • When you export data in SAP does it create a temporary .xls file anywhere? If the file is created on the fly you can’t use application.windows because the other workbook will be opened in a separate instance. I think it would make sense to save the file and then you can access it by the path rather than using application.windows.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.