+ Reply to Thread
Results 1 to 10 of 10

inputbox appear when cells outside the set range are modified

  1. #1
    Registered User
    Join Date
    11-11-2014
    Location
    Manchester,England
    MS-Off Ver
    8.1
    Posts
    6

    inputbox appear when cells outside the set range are modified

    Hi guys (and girls),

    Im having a slight but annoying issue on excel.

    I have a form that will be used to log orders onto, the basic premise is that you select a product code from a drop down list and vlookup populates the description in the next cell. Im trying to add very basic validation using vba, so that when a product from the drop down list is selected an input box appears asking for the quantity and the puts that value in the corresponding quantity cell.

    the problem is that with my current code, even if I specify only one cell as my range, when I change any of the dropdown lists the input box appears and changes the value in the first rows quantity box.

    the current code is:
    Please Login or Register  to view this content.
    any help will be greatly appreciated thanks!
    Last edited by jv160591; 11-12-2014 at 08:19 AM. Reason: didnt add code tags

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: inputbox appear when cells outside the set range are modified

    When do you want the input box to appear?

    By the way, there's no such thing as Application.WorksheetFunction.String.

    PS Can you add code tags when posting code?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    11-11-2014
    Location
    Manchester,England
    MS-Off Ver
    8.1
    Posts
    6

    Re: inputbox appear when cells outside the set range are modified

    Hi Norie,

    I would like it so that when a product code is selected a input box appears and that value is saved on that row.

    at the moment, the code is meant to only effect the first row, however if I choose a product on any of the rows I can input a quantity for the first row.

    when I had it working on one row I was going to copy and paste and modify it so that no matter which row you use, when you select a product code an input box appears and changes the value for that row only.

    I hope that makes sense?

    thanks

  4. #4
    Registered User
    Join Date
    11-11-2014
    Location
    Manchester,England
    MS-Off Ver
    8.1
    Posts
    6

    Re: inputbox appear when cells outside the set range are modified

    Hi Fotis,

    Thanks and apologies, I have now amended the original post.

    Thanks

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: inputbox appear when cells outside the set range are modified

    Do you have multiple dropdowns/data validation and you want this code to run whenever a selection is made in one of them?

    Also, do you want the value entered in the inputbox by the user to be placed on the worksheet in the quantity cell corresponding to the dropdown the selection was made in?

  6. #6
    Registered User
    Join Date
    11-11-2014
    Location
    Manchester,England
    MS-Off Ver
    8.1
    Posts
    6

    Re: inputbox appear when cells outside the set range are modified

    Hi Norie,

    yes to both of your questions.

    I've attached a screenshot of the worksheet to give you a better idea of the setup.

    I hope that helps a bit?

    Untitled.jpg

    thanks

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: inputbox appear when cells outside the set range are modified

    Which column are the drop downs in?

  8. #8
    Registered User
    Join Date
    11-11-2014
    Location
    Manchester,England
    MS-Off Ver
    8.1
    Posts
    6

    Re: inputbox appear when cells outside the set range are modified

    The drop ones are in the first column which is Column C and the Vlookup data Is in the second column which is column E.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: inputbox appear when cells outside the set range are modified

    With this code whenever a selection is made in a dropdown in C2:C50 the user will be prompted for a quantity.

    The quantity they enter will go in column F on the same row as the dropdown the made the selection in.
    Please Login or Register  to view this content.
    Or, that's what should happen, hopefully.

  10. #10
    Registered User
    Join Date
    11-11-2014
    Location
    Manchester,England
    MS-Off Ver
    8.1
    Posts
    6

    Re: inputbox appear when cells outside the set range are modified

    Norie, you absolute gem, it works like an absolute dream! thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Getting the worksheet from which a range was set using the inputbox
    By SidewinderAM9M in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-16-2013, 10:55 PM
  2. Display Message Box if any cell value in range is modified
    By eddieD95 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2012, 10:31 AM
  3. When cell is modified Hide Entire Rows in a Range
    By Hudas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-09-2012, 09:43 AM
  4. VBA - Generate Date Range Within 2 Supplied Dates (Modified)
    By AErmie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-12-2009, 06:49 AM
  5. Selecting a range with an inputbox...
    By Rutgers_Excels in forum Excel General
    Replies: 0
    Last Post: 04-08-2005, 03:25 PM

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