First of all, do you know if the values of FormSource and Dimensions is available to your sheet macro?
try adding: msgbox "FormSource: " & formsource & chr(13) & "Dimensions: " & Dimensions before your Myform.Height line and see if they have values. Your next questions: How to exit the form whenever I deactivate the sheet or How to exit the form whenever I close the workbook There are events: Worksheet_Deactivate and Workbook_BeforeClose that you can create and include: unload MyForm Paul ________________________________ From: kanwal_no1 <kanwal...@gmail.com> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Wednesday, March 11, 2009 8:29:55 AM Subject: $$Excel-Macros$$ Re: VBA Form Auto Sizing Hi Ashish, Thanks a lot ! But I am afraid, it is not working... Just want to mention that I put the following code in the sheet1 Private Sub CommandButton1_Click() MyForm.Show End Sub Private Sub Worksheet_Change(ByVal Target As Range) MyForm.Height = "=HLOOKUP(FormSource,Dimensions,3,FALSE)" MyForm.Width = "=HLOOKUP(FormSource,Dimensions,2,FALSE)" End Sub Form is appearing correctly, but on selection change it is giving run time error 13, Type Mismatch...... Any guess why ? Regards Kanwaljit --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
