DialogSheets/Edit Boxes (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

You could use a UserForm, but the following might suffice :

ActiveSheet.Range("A1").Value = InputBox("Enter Exchange Rate")

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

Reply | Quote

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

Reply | Quote

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 VBATUTOR.COM. However you can also come back here for any help you require.

Andrew

Reply | Quote

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

Reply | Quote

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

Reply | Quote