Hi Ashish, I tried the code, but it is not working the way it should...... I have uploaded a file here http://groups.google.com/group/excel-macros/web/VBA%20form%20auto%20resizing%20%282%29.xls Infact the number of sets can increase. So what ultimately is desired that formwidth and Formheight are calculated by the following formulae based on what is contained in Cell A1 (named as FormSource)
FormWidth=HLOOKUP(FormSource,Dimensions,2,FALSE) FormHeight=HLOOKUP(FormSource,Dimensions,3,FALSE) It will help if new Datasets are added. I forgot to mention that the size is in mm (millimeters) On Mar 5, 9:15 am, Ashish Jain <26may.1...@gmail.com> wrote: > <http://www.excelitems.com/2009/03/resize-form-using-worksheet-event-m...>---------------------------------------------------------------------------------------------------------------------------- > > Hi Kanwaljit, > > I've posted your solution, please check this > link:http://www.excelitems.com/2009/03/resize-form-using-worksheet-event-m... > > For more tips, tricks, downloads and problem solutions: visit > -->http://www.excelitems.com > ------------------------------------------------------------------------ > Regards > *Ashish Jain* > Analyst and Demand-Co-ordinator, CSC Australia > Microsoft Certified Application (Excel) Specialist > Technical Trainer - Excel and VBA > Author ->www.excelitems.com > 09999-40-48-43 > ---------------------------------------------------------------------------------------------------------------------------- > ---------------------------------------------------------------------------------------------------------------------------- > > On Wed, Mar 4, 2009 at 12:25 PM, kanwal_no1 <kanwal...@gmail.com> wrote: > > > Hi Everyone, > > > Regards... > > This is my 1st post to this group......... > > > I have a User Form which needs to take a specified size depending upon > > the data source. Data Source is in Range DataS1 and DataS2. The > > following range (named as "Dimensions") shows the dimensions which the > > User Form needs to have when ever we use DataS1 or DataS2. > > > UserFormSpec DataS1 DataS2 > > FormWidth 50 100 > > FormHeight 40 80 > > > Cell A1 of Sheet1 contains a dropdown containing the names of the > > datasources, i.e., DataS1 and DataS2. The following code is used to > > show the UserForm. > > > Private Sub MyForm_Click() > > UserForm.Show > > End Sub > > > What is wish is that when I select DataS1 or DataS2 from Cell A1, the > > UserForm width and height changes according to the numbers in range > > "Dimensions". > > > 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 athttp://www.excel-macros.blogspot.com > > 3. Learn VBA Macros athttp://www.vbamacros.blogspot.com > > 4. Excel Tips and Tricks athttp://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 > > > ------------------------------------------------------------------------------------- --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
