+ Reply to Thread
Results 1 to 14 of 14

VBA User input or set value. URGENT

  1. #1
    Registered User
    Join Date
    05-08-2015
    Location
    Ausralia
    MS-Off Ver
    2013
    Posts
    41

    Post VBA User input or set value. URGENT

    Hey guys so I have a cell say A1 that has a list with 3 options (1, 2, 3).
    Cells A2 and A3 both have IF statements that change their values depending on the value of A1.
    Im trying to add another option "Custom" in the A1 cell, which would then allow you to input your own data (a number value) into cells A2 and A3.
    Would really like some help with this, im not really versed in VBA either i know a little but hopefully someone on this forum could help me.

    Thanks
    Nick

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA User input or set value. URGENT

    how do you propose the user enter the values for custom? via inputbox?

    also how are you coding the 1,2,3 options
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    05-08-2015
    Location
    Ausralia
    MS-Off Ver
    2013
    Posts
    41

    Re: VBA User input or set value. URGENT

    Well in A1 "custom" will be an option. Then for the 1,2,3 options its just a list in data validation. And input box I'm guessing. Don't really know how to approach it.

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA User input or set value. URGENT

    how are you triggering it
    are you even using VBA for any of it right now? if so what code are you using?

  5. #5
    Registered User
    Join Date
    05-08-2015
    Location
    Ausralia
    MS-Off Ver
    2013
    Posts
    41

    Re: VBA User input or set value. URGENT

    No VBA as of yet. And its just a drop down list and IF statements

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA User input or set value. URGENT

    oh
    i assume you want to VBA it all then
    see attached to see if it does what you require

    you will need to update the code for 1,2,3
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-08-2015
    Location
    Ausralia
    MS-Off Ver
    2013
    Posts
    41

    Re: VBA User input or set value. URGENT

    I'm assuming you mean my own values, I tried updating them, but when i selected the a number in A1 nothing happened.

  8. #8
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA User input or set value. URGENT

    the DV is 1,2,3,Custom like you said


    you need to change the code

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-08-2015
    Location
    Ausralia
    MS-Off Ver
    2013
    Posts
    41

    Re: VBA User input or set value. URGENT

    Table 1.xlsm

    Thats the file im trying to edit, so the door type has four options but when the custom option is enabled i cant input my own value in the height and width. :/

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA User input or set value. URGENT

    you cant just paste the code i provided in the sheet and put into new one..all the references are different
    try this
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-08-2015
    Location
    Ausralia
    MS-Off Ver
    2013
    Posts
    41

    Re: VBA User input or set value. URGENT

    Awesome that worked wonders, thanks a lot man :D just one quick question would there be an easy way to have it work over multiple columns without having to copy it and change the values each time? like could i say

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("D33:Z33").Address Then
    Select Case Range("D33Z33").Value

    Case "2.1m x 0.9m"
    Range("D41:Z41").Value = 0.9
    Range("D42:Z42").Value = 2.1

    Case "2.7m x 2.4m"
    Range("D41:Z41").Value = 2.4
    Range("D42:Z42").Value = 2.7

    Case "4.5m x 2.4m"
    Range("D41:Z41").Value = 2.4
    Range("D42:Z42").Value = 4.5

    Case "Custom"
    Range("D41:Z41").Value = InputBox("Input Widtht.")
    Range("D42:Z42").Value = InputBox("Input Height.")

    End Select

    End If

    End Sub


    or would that just mean that if and of the cells D33 to Z33 had an option selected that it would fill all of the others?

  12. #12
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA User input or set value. URGENT

    please use code tags when posting code
    [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.


    Please Login or Register  to view this content.
    code now works for single cells from D to Z however you if you copy and paste or try to do change multiple values at once
    ie d3 fill right to z3
    only the first one will work
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    05-08-2015
    Location
    Ausralia
    MS-Off Ver
    2013
    Posts
    41

    Re: VBA User input or set value. URGENT

    Ah sorry new here didnt know you could do that, thank you so much man really helped me out :D

  14. #14
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA User input or set value. URGENT

    yeah understand just letting you know for future reference

    not a problem

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Data Input Form with number of line items based on user input
    By j_gideon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2013, 02:54 PM
  2. URGENT help with user input and displaying the data
    By hassanmir in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-30-2013, 12:15 PM
  3. Pulling data from another excel file using user input (URGENT)
    By NissanGTR in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-25-2012, 12:01 AM
  4. Automatic Formula Input Based on User Input Using a Macro
    By Drayloc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-03-2012, 12:10 PM
  5. Using text box input to lookup number and replace based on user input into new column
    By harl3y412 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2011, 03:15 PM

Tags for this Thread

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