+ Reply to Thread
Results 1 to 22 of 22

Userform w/textbox search a table for a value on click

  1. #1
    Registered User
    Join Date
    03-25-2011
    Location
    Roanoke, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Userform w/textbox search a table for a value on click

    I have a workbook/worksheet I wish to use to input Treasury futures prices in order to calculate a running P&L with. There is an Entry Price cell and an Exit Price cell which calculates and returns a money value into a P&L cell. I have all the math in place already. Treasury future quotes are as such: "121'050" with the next up tick being "120'055." These two quotes stand for 121 5/32 and 121 11/64. 121 is called the "handle" with 050 and 055 called the "tick".

    I have a UserForm which opens when I click in any Entry Price or Exit Price cell on my spreadsheet. The UserForm has TextBox1 ("Handle") and TextBox2 ("Tick") and a CmdButton (OK). I have a table listing each "Tick" with the corresponding 32nd or 64th fractional value.

    I need to run a VBA script so when I click the CmdButton (OK) the entered TextBox2 value (Tick) searches the table or range of cells for that Tick, then references the corresponding 32nd or 64th, then adds that fraction to the Handle value from TextBox1 and inserts the completed number (e.g. 121 5/32) into the Entry Price or Exit Price cell I originally clicked.

    Thanks in advance for all help! (I am new to VBA and need a little help with this.)
    Last edited by trader07; 03-26-2011 at 11:08 PM. Reason: cleaned up explanation/question

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Userform w/textbox search a table for a value on click

    Hi trader07,
    Welcome to the forum.
    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Registered User
    Join Date
    03-25-2011
    Location
    Roanoke, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Userform w/textbox search a table for a value on click

    I am not sure how to attach a screen shot (.gif) of my spreadsheet. The file is to big to meet the limitations provided under the site instructions. I definitely need some help though.

    trader07

  4. #4
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Userform w/textbox search a table for a value on click

    Hi trader07
    screen shot are a pain as no is going to duplicate the sheets
    can zip or reduce the file size?
    or expand on "searches the table or range of cells for that Tick, then references the corresponding 32nd or 64th" as it really does not mean anything when you dont know the layout.. bit of an art in asking a question and including the right infomation

  5. #5
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Userform w/textbox search a table for a value on click

    Hi trader07
    The code would be something like
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-25-2011
    Location
    Roanoke, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Userform w/textbox search a table for a value on click

    There are two columns : Entry Price (H2:H100) and Exit Price (K2:K100).
    When I click on any cell in either of those two columns a UserForm pops up.
    There are two TextBoxes one named "Handle" (TextBox1) and one named "Tick" (TextBox2). Of course there is the CmndButn (OK).

    After I have entered a text string in TextBox1 and TextBox2 I click the OK button. TextBox2 text string (050) needs to find/look/search through the range cells O2:O65 and find the matching text string; then grab the corresponding fractional value from the cell located range P2:P65 (e.g. TextBox2 value is 050 which is located in cell O11 and it corresponds with cell P11 with the fractional value 5/32.)

    Next I need the fractional value of 5/32 to be added back to the TextBox1 text string (121 in this case) to give me a completed number with fraction, (e.g. the TextBox1 value was 121 so I need to end up with the number and fraction of 121 5/32).

    Last I need the number and fraction 121 5/32 to insert into the original Entry Price (any cell in the range of H2:H100) or Exit Price (any cell in the range K2:K100) which I originally clicked to activate the UserForm. I have math that uses the number with fraction in the Entry Price and Exit Price cells which delivers to a Gross/Profit Loss cell.

    Does this help any? I see your script above but don't get it.
    Last edited by trader07; 03-27-2011 at 08:50 PM.

  7. #7
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Userform w/textbox search a table for a value on click

    Hi trader07,

    try this for you command button code
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    03-25-2011
    Location
    Roanoke, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Userform w/textbox search a table for a value on click

    Thanks Pike but that isn't working. When I click the CmndBtn1 (OK) after typing in my text string nothing happens. What next?

  9. #9
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Userform w/textbox search a table for a value on click

    Hi trader07

    can you attach a small sample file ?

  10. #10
    Registered User
    Join Date
    03-25-2011
    Location
    Roanoke, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Userform w/textbox search a table for a value on click

    Pike is there anyway to exchange emails, then I can send you a copy of the .xlsm? I am close to just giving up on this. I can't believe this is that hard for an experienced VBA person yet no one other than you has stepped up. Thank you! Let me know about emailing this file. I have tried several times to get a smaller file size in order to post on here and I cannot get the bytes down without needing a magnifying glass to read it.

    trader07

  11. #11
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Userform w/textbox search a table for a value on click

    no, just delete the sensitive infomation and any sheets not needed
    you only need to leave a few columns

  12. #12
    Registered User
    Join Date
    03-25-2011
    Location
    Roanoke, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Userform w/textbox search a table for a value on click

    Try this, thank again

  13. #13
    Registered User
    Join Date
    03-25-2011
    Location
    Roanoke, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Userform w/textbox search a table for a value on click

    I don't get it, I uploaded a .xlsm file but it did not post on here??

  14. #14
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Userform w/textbox search a table for a value on click

    To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
    To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

    On this page, below the message box, you will find a button labelled 'Manage Attachments'.
    Clicking this button will open a new window for uploading attachments.

    You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
    Alternatively you can click the Attachment Icon to open this page.

    To upload a file from your computer, click the 'Browse' button and locate the file.

    To upload a file from another URL, enter the full URL for the file in the second box on this page.
    Once you have completed one of the boxes, click 'Upload'.

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  15. #15
    Registered User
    Join Date
    03-25-2011
    Location
    Roanoke, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Userform w/textbox search a table for a value on click

    I did all that, the first time, and now a second time. Don't get it?

  16. #16
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Userform w/textbox search a table for a value on click

    can you zip the file?

  17. #17
    Registered User
    Join Date
    03-25-2011
    Location
    Roanoke, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Userform w/textbox search a table for a value on click

    Pike are you still available? I got you code to run but there are a couple glitches yet unresolved. I have tried to attach the WorkBook with this post as well but I get a meesage that says "invalid file." IT is a .xlsm workbook that is about 32 k in size. What gives???

    1 - Your search result was correct, but I need the search result added to the Me.TextBox1.Value then entered into the original cell under column H ("Entry Price") or column K ("Exit Price") that initiated the UserForm1 popup to start with. I don't want the result to end being posted in the TextBox1 field or especially the searched for and found cell in my table - it need remain undisturbed.



    Trader07

  18. #18
    Registered User
    Join Date
    03-25-2011
    Location
    Roanoke, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Userform w/textbox search a table for a value on click

    I hope this is it.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    03-25-2011
    Location
    Roanoke, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Userform w/textbox search a table for a value on click

    Pike now that you can get your hands on the workbook let me describe in sequence what I want to happen.

    1 - I start on the first empty row (my next record set) and enter the values under each Column as I tab across. Each row down the sheet will be a new record set.

    2 - When I get to the "Entry Price" column (Range H2:H100), I click the empty cell and UserForm1 pops up.

    3 - I then make my Handle text string (TextBox1) and my Tick text string (TextBox2) entries.

    4- Upon click "OK" the TextBox2.Value searches between O2:O65 and upon finding the match, takes the fractional value in the cell immediately to the right and adds it to the TextBox1.Value.

    5 - The resulting number then populates into the record set cell (H2:H100) I originally clicked to start the ball rolling.

    6 - Later once my trade has closed, I will want to do the same process for the corresponding "Exit Price" record set (K2:K100). Then all my math kicks in tracking my P&L.

    7 - Each time after the Entry Price or Exit Price record has populated I want TextBox1&2 to clear themselves and UserForm1 to close /unload. That is it!

  20. #20
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Userform w/textbox search a table for a value on click

    trader07
    I would use double click in the worksheet as its empty
    Please Login or Register  to view this content.
    and change the userform to
    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    03-25-2011
    Location
    Roanoke, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Userform w/textbox search a table for a value on click

    Pike that is getting very close but there is still one glitch. In my table Column P to be exact, all the fractions return as a decimal value - and that is fine - EXCEPT; there is a 0 (zero) before the decimal point which gets concatenated with the Handle and that fouls up the calculations in the other cells. e.g. 121 1/32 post as 121 0.3125. This will not work. I need to lose the 0 (zero) setting in from of the decimal point. I looked at some cell formats within the standard Excel 2007 dialog boxes but everything wants to put a 0 (zero) place holder in front of the decimal. I got to lose that 0 (zero) place holder!

    Other than this issue it works fine and THANK YOU!

  22. #22
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Userform w/textbox search a table for a value on click

    did not see the fraction format
    Please Login or Register  to view this content.
    probably format the textbox1 as fraction too

+ 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