Posts by PeteNZ

    Re: Delete Button In Word


    One way to do this is to highlight the the buttons and set the font style to "hidden". Still means the buttons are available should you wish to re-run vba but will not be shown on printed document

    Re: Add Next Number In Sequence & TextBox Text


    Hi Dave, thanks for the code snippet


    I may not have explained myself clearly. The list of codes is pulled from a database an automatic update is run on file open. Therefore the list of codes changes regularly. The database list has missing sections of codes, i.e. jumps from 1012.00 to 3000.00, therefore missing everything from 1013.00 to 2999.00 out. Is there a way to use the next available number (based on the above), not the next number after the last in the list?


    I have an additional sheet (numbered 0000 to 55000) which does a lookup to see whether the base code has been used (flags used as "Y" and unused as "X"), but does not go as deep as the .01, .02 codes. I tried using a lookup for these but when you're talking about 55000 base codes and 99 possible subcodes, lookups on over 5.4 million cells really makes your computer grind to a halt.


    Ideally, when the next code is selected it will be held in a "holding" sheet and when the workbook is updated from the database, if the data in the holding sheet matches a "new" entry in the database, the entry in the holding sheet will be removed.


    A bit confusing I know but I'm working with an old database and want to keep the client code length to 4:2 so using "unused" codes is required.


    Any thoughts on better, faster, simpler ways to do this greatly appreciated.

    I have worksheet with a column of client codes. These are made up of:
    (a) a "base code", being a minimum four to five digit, i.e.


    0000
    0001
    0002, etc


    (b) plus a decimal point and a "sub code" (i.e.


    .00
    .01
    .02
    .03 through to .99)


    The worksheet also contains the client name (i.e.


    1031.00 John Smith
    1031.01 Mary Smith
    1032.00 Jack Brown
    1033.00 My Company etc


    As the supplier codes list has been manually done, there are numerous "gaps" in base code numbers as well as the subcodes.


    I am looking to have a userform that a user can:


    find the next unused base code and apply the 00 subcode (with client name).


    Any help greatly appreciated.


    PeteNZ

    Re: Delete A Form Text Box With A Macro


    Sorry, you're using Word?


    Try this



    Cheers


    Pete

    Re: Add Totals If In Month


    Hi Bunny1


    Try this:


    Code
    =SUMPRODUCT(--(A1:A50>=DATE(2002,1,1)),--(A1:A50<=DATE(2002,1,31)),B1:B50)


    Column A being the column containing dates; column B containing amounts.

    Re: Word Mmerge Field Format


    Hi Gavin


    How about trying to get the data you want via excel first, i.e. add a column to your spreadsheet and use the formula as shown below, i.e. your data is in A1 and you want to show just the last 8 characters in B1. You can then refer to column B for your mailmerge.

    Code
    =RIGHT(A1,8)


    Cheers
    Pete

    Re: Changing Orientation On All Worksheets For Printing Macro


    Hi


    Here's an option. In VBA project explorer select 'This Workbook' Select Workbook from the dropdown in the left of the taskbar and 'BeforePrint' from the right dropdown.


    Paste this before the 'End Sub' statement


    Code
    ActiveSheet.PageSetup.Orientation = xlLandscape


    or

    Code
    ActiveSheet.PageSetup.Orientation = xlPortrait


    for portrait.


    Note: You will ned to print preview (or print) to see the change in the sheet layout.


    Cheers


    Pete

    Re: Different Font Colours For Data Entered Through Formula


    you could use vba to specify a colour based on length i.e.


    Code
    Sub Macro1()
    ActiveCell.Characters(Start:=1, Length:=4).Font.ColorIndex = 3
    ActiveCell.Characters(Start:=5, Length:=13).Font.ColorIndex = 5
    End Sub


    it's a start I guess

    Re: Can't Hide Table Boundaries In Word Document


    Question, do you want the table borders to be shown (when printed)? If you have a table with no "visible borders" you are probably looking at the gridlines (which you can see in print layout but not in print-preview). Turn this feature on or off through the "table" menu, "Show Gridlines" or "Hide Gridlines".
    Regards
    Pete

    Re: Macro Word: Put Space Between Characters For Selected Paragraph


    Why don't you use font character spacing instead of trying to insert a space between each character. This will mean you can type the document normally, spell-check, etc will work and you can format appropriately.


    Below is sample code that may help - note, you need to be at start of paragraph for this to apply to whole paragraph. Change the spacing to suit.


    Code
    Sub Spacer()
        Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdExtend
        With Selection.Font
            .Spacing = 4
            .Scaling = 100
        End With
    End Sub

    Re: Macro To Convert Table To Text In Word


    Below code may help with the convert to text issue.

    Code
    Selection.Rows.ConvertToText Separator:=wdSeparateByParagraphs, _
            NestedTables:=True


    Wonder why you need to put into Word?
    Cheers
    Pete

    Re: Mail Merge


    In the excel file, does the first record you are merging have a blank (i.e. you have a blank cell as part of the merge data. If there is a space in a "blank" cell this can come through into your word merge. A bit more detail will be necessary to answer your question

    Re: Creating Dynamic Ranges Firstcolumn To Headings 2nd Column To Matching Values


    Hi Dave
    simply wanting data to be entered into the Companies sheet. From that creating dynamic listings (as shown but not automatically done, yet) in the Lists sheet. Looking to have one point of data entry for Companies/Branches.
    This data will then flow back to the Data sheet to select Companies/branches, and enter other data which is relevant to them.
    The Lists sheet uses dynamic names (via code in "thisworkbook"). It's getting the data from Companies to Lists in a logical order that's the problem. And then I'll want to sort each column in the lists sheet into alphabetical order.


    Your help much appreciated.


    Cheers


    Pete


    Update: Figured it out. Will attach an example spreadsheet once I've done some further mods. Combination of dynamic ranges and multicriteria index/match validation lists.

    Re: Creating Dynamic Ranges Firstcolumn To Headings 2nd Column To Matching Values


    Thanks Dave, am having a brainfreeze on this one. Firstly getting the companies from Sheets("Companies") to Sheets("Lists") across the top row, and then getting the matching branches to be shown below each company is my problem.
    Pete

    I have a problem that i'm sure is solveable. I have a sheet with column1 which contains company names, column2 contains the branches, columns3+ contain address details for each branch. There are some companies with many branches (each company has at least one branch).


    I want to be able to enter in new companies and/or branches and have these populate a series of pulldown lists (i.e. select Company1, then the branch). I would like to use a dynamic range scenario so that if a company and/or branch are added, the pull-downs are updated with the new data.


    I have attached an example of the data. The Sheet "Data" contains the drop downs. The sheet "Companies" contains all details of the companies. The sheet "Lists" contains the lists (the data has been manually entered on this sheet which is what I'd like to automate).


    Cheers


    Pete