+ Reply to Thread
Results 1 to 5 of 5

Editing a macro to change the row height in a merged cell

  1. #1
    Registered User
    Join Date
    04-27-2005
    Posts
    9

    Editing a macro to change the row height in a merged cell

    I orginally posted this inquiry in Excel General but now that the answer is clearly VBA programming I was hoping for some assistance from this room. The original post is

    http://www.excelforum.com/showthread.php?t=649936

    I am very novice in programing with VBA and I have been provided a macro that needs editing. It is very important to get the spacing correct on the sheet that I am working, and unfortunately there is no way to getting around using a merged cell. The problem:

    I have a Merged cell that is using a concatenate formula and depending on the circumstances has the possibility to be anywhere from 1 to 7 lines long. Since the cell is a merged cell it will not autofit.

    The solution that was provided to me was:

    Please Login or Register  to view this content.
    This solution is a good start but I have a problem that I beleive can be edited out of the program but I do not know where to start.

    Problem 1:
    My merged cell has the possibility to be between 1 and 7 lines.
    If the Merged Cell is:

    1 Line, Macro Result = 15 point height (correct)
    2 Lines, Macro Result = 45 point height (incorrect should be 30)
    3 Lines, Macro Result = 75 point height (incorrect should be 45)
    4 Lines, Macro Result = 75 point height (incorrect should be 60)
    5 Lines, Macro Result = 105 point height (incorrect should be 75)
    6 Lines, Macro Result = 135 point height (incorrect should be 90)
    7 Lines, Macro Result = 150 point height (incorrect should be 105)

    Problem 2: (minor cosmetic)
    When the input data goes from large to small the cell height does not reduce in size.

    Any assistance in editing this macro would be greatly appreciated
    Last edited by ordoff73; 07-16-2008 at 10:22 AM.

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    ordoff73

    Please take a couple of minutes and read the Forum Rules then wrap your VBA code (Rule 3)

    I have also closed your other thread
    Thanks for adding the reference to the other thread & the comments of why you have posted a 2nd thread
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Always avoid Merged Cells they will generally end up causing problems. Maybe use a TextBox instead
    Last edited by royUK; 07-16-2008 at 09:30 AM.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    04-27-2005
    Posts
    9
    Thank you for your response,

    however I am unaware how a textbox can repair my problem.

    I have attempted to insert a textbox in replacement of my merged cell. My problem with the textbox is;

    1. I do not know how to get the results of the formula to appear in the textbox. (when the concatenate formula that I am using is written into the textbox it shows up exactly as typed not the result of the formula).

    2. when the textbox autofits to the size of the data in the textbox it does not adjust the cells surrounding it, because spacing is very important in this document. This is why I thought I had no choice but to merge the cells.

    Thank you for you input and your time.
    Last edited by ordoff73; 07-16-2008 at 10:22 AM.

  5. #5
    Registered User
    Join Date
    04-27-2005
    Posts
    9
    I have created a work around for this problem. In the protection of the sheet I have allowed the user to format the row. In the cell that needs adjusted I have inserted detailed instructions on adjusting the row height manually on a comment attached to the cell similiar to the details above. I came to the conclusion that I must put some trust in the end user. This also eleminates macros in this workbook, which most users have their security settings set high to prevent the use of macros as a default. Thank you all for your input.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1