Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Input Box (MS Excel 2000)

    Good afternoon

    I am not sure if an input box is the actual answer to the problem that I have, which is

    I have one lookup box to look up 2717 UK postcode areas, i.e. AB10, E1, TW19 etc. The second looks up a vehicle size from a selection of 4. I have a cell with a formula that recognises the postcode, the vehicle size and then looks up a range of rates according to the criteria selected.

    I find that by using the lookup box for the postcodes cumbersome and long winded and wanted to find an alternative solution whereby I could just type in the postcode to find it. I tried finding info about making my own search box on the form but could'nt really find anything so I thought that an input box might do the trick just as easily although I would prefer to not really use a macro.

    To try and aceive this I have typed the following macro

    Sub postcodes()
    Dim varinput As Integer

    varinput=InputBox("Please enter the postcode","Postcodes")
    Sheets("intro").Range("A1:A2717").value=varinput

    End Sub

    However I get runtime error '9'. subscript out of range

    Assuming this is (a) the right solution to my problem, and that ([img]/forums/images/smilies/cool.gif[/img] somebody could help me get the input box working, how can I

    1. Have the input box showing always on the sheet
    2. Link the input box to a cell so that my formulas will work.

    TIA


    Steve

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Input Box (MS Excel 2000)

    Why not type the postcode directly into a cell? That's much easier than displaying inputboxes etc.

    The problem with your code is that you declared varinput as an Integer, i.e. a whole number in the range -32,768 to +32,767. You should declare it as a String instead, to accomodate text values such as AB10.
    It is not possible to have an inputbox open all the time. The user MUST click OK (or Cancel) before continuing to work with the worksheet.

  3. #3
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input Box (MS Excel 2000)

    Thanks Hans

    and please excuse my ignorance as I have only been using Excel for a few months, my intention is to have all material on the sheet hidden except the parts that show postcode, vehicle and the result. When you say type the postcode directly into a cell is there some sort of code that I need to use so that it looks up the right row from A23:A2717.

    Thank you

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Input Box (MS Excel 2000)

    I'm sorry, I don't understand. What do you mean exactly?

  5. #5
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input Box (MS Excel 2000)

    Hi Hans

    In an earlier post you said

    "Why not type the postcode directly into a cell? That's much easier than displaying inputboxes etc."

    A23:A2717 contains all of the UK postcode areas, depending on which of these is selected and which vehicle is selected the rate is calculated, as mentioned I presently have the postcodes displayed in a listbox linked to a cell.

    When you said why not type the postcode directly into a cell I made the assumption that this would in some way look up my postcodes from A23 to A2717, I hope that makes sense

    Thank you

    Danny

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Input Box (MS Excel 2000)

    Say that the cell linked to the list box is P36. This cell is used in your formulas. I gather that you want to keep P36 hidden from the user. So let the user type the postcode into a convenient cell, say B2. In cell P36, enter the formula =B2. If the user types a postcode, say AB10, into cell B2, cell P36 will automatically contain the same value, and this will be used in your formulas. HTH

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Input Box (MS Excel 2000)

    >>> Is there some sort of code that I need to use so that it looks up the right row from A23:A2717!

    You can use the MATCH Function to give a row number where it finds the match of Post Code! See a simple example in the attachment!
    Regards,
    Rudi

  8. #8
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input Box (MS Excel 2000)

    Thanks Rudi

    That is almost what I am looking to do, could I trouble you to look at the attached zip and advise how I would need to do this.

    Thanks

  9. #9
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input Box (MS Excel 2000)

    Thanks Hans

    I think I confused the issue a little, I think something along the lines that Rudi has suggested would work for me.

    Thanks for your input

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Input Box (MS Excel 2000)

    Hi Danny,

    Could you attempt to attach the zip file again! I seem to have some problems opening the file!
    Regards,
    Rudi

  11. #11
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input Box (MS Excel 2000)

    Maybe this will work

    Thanks

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Input Box (MS Excel 2000)

    Sorry Danny...Still the same problem. Hopefully someone else can open it and assist.

    Can you try and make a smaller version of the file. Save that and attach it without using WinZip. Just draw up a small verion with dummy data if necessary. (Sorry about the inconvenience!)
    Regards,
    Rudi

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Input Box (MS Excel 2000)

    It was either a temporary glitch, or the problem is on your side. I have no problem downloading the file.

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Input Box (MS Excel 2000)

    Seems its on my side. The problem still persists! <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Regards,
    Rudi

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Input Box (MS Excel 2000)

    Here goes. I have simply removed the list box for postcode, and changed the lookup formula to
    =VLOOKUP(A1,A20:M30,C1+9,FALSE)
    This returns #N/A if you enter a non-existing postcode, so I added error checking:
    =IF(ISNA(VLOOKUP(A1,A20:M30,C1+9,FALSE)),"",VLOOKU P(A1,A20:M30,C1+9,FALSE))
    See attached workbook.

Page 1 of 2 12 LastLast

Posting Permissions

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