Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DialogSheets/Edit Boxes (Excel 2000)

    I am trying to create a dialog box which asks the user to enter an foreign exchange rate and then puts that rate into a certain cell in an Excel report. You used to be able to do this with a Dialog sheet but now when I try, it says that the EditBoxes property is not supported. What replaced the EditBoxes property in Excel 2000? What is the best way to go about this now?

    Thanks in advance,

    Christa

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DialogSheets/Edit Boxes (Excel 2000)

    You could use a UserForm, but the following might suffice :<pre>ActiveSheet.Range("A1").Value = InputBox("Enter Exchange Rate")</pre>

    You can change the reference to A1 to the appropriate cell in your sheet.

    You could attach that code to a control button on the sheet, or include it some other code you might have.

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DialogSheets/Edit Boxes (Excel 2000)

    Thanks, Andrew...

    That works...It would be better if I could have two "input" sections in the dialog box...one for the spot rate and one for the average rate. To do this, I would have to use a Userform, correct? I know how to set up a UserForm...I just haven't figured out how to get the information "out" of the UserForm into the spreadsheet. Do you know of any reference materials that are available on the web or any books I could get that would help me learn more about VBA/UserForms etc.

    Thanks again,

    Christa

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DialogSheets/Edit Boxes (Excel 2000)

    You could use a similar syntax as the input box. Once you have the data in the userform, you could assign it as follows :

    Sub AssignValues()
    UserForm1.Show
    Range("A1").Value = UserForm1.TextBox1.Value
    Range("B1").Value = UserForm1.TextBox2.Value
    UnLoad UserForm1
    End Sub

    That is very basic, but should give you the idea. I am sorry I don't know off hand of any references specially about userforms on th eweb, but youmight try <A target="_blank" HREF=http://www.vbatutor.com/>VBATUTOR.COM</A>. However you can also come back here for any help you require.

    Andrew

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DialogSheets/Edit Boxes (Excel 2000)

    Thanks for you help, Andrew...I tried to run this code with my userform (first having it in Module1...then putting it in Workbook_Open). Both of these bring up the Userform but then get stuck there...the values do not go into the spreadsheet and the form does not unload.

    I then tried putting the parts of the macro into TextBox1_Change (Range("A1").Value = UserForm1.TextBox1.Value) and TextBox2_Change (Range("B1").Value = UserForm1.TextBox2.Value) and then creating OK and Cancel buttons to unload the form. This works until I unload the form...when I unload it the amounts in cells A1 and B1 disappear. What am I doing wrong?

    Thanks for the like to the VBAtutor site. And thanks for the offer to keep coming back here. I think this lounge is great...very helpful! But since I don't know much about VBA yet, I not any help to others in the lounge and sometimes I feel bad always "taking" and never "giving back" (answers).

    Thanks again,

    Christa

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DialogSheets/Edit Boxes (Excel 2000)

    I think I figured it out...I had left the whole macro in Workbook_Open...when I commented out all the lines except UserForm1.Show...it worked.

    I still think I may have achieved this in a roundabout way so if you have a better way to do it (other than breaking up the macro into all those "sections")...please let me know.

    Thanks,

    Christa

Posting Permissions

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