Solved

Excel/VB EditBox: assigning variable name to inputs

Posted on 1997-08-19
7
397 Views
Last Modified: 2008-03-17
I have created a DialogBox with a number of different EditBoxes. In these EditBoxes, I wish to input data to which I would like to assign a variable name, later to be used in my code. What I have written so far looks as below (if I could get it to work, I'd be set,since this code would mean that I have successfully assigned EditBox inputs to a variable).

Sub DialogBox()
>     DialogSheets("Dialog1").EditBoxes(1).Text = 100
>     DialogSheets("Dialog1").EditBoxes(2).Text = 12
>     DialogSheets("Dialog1").Show
>     Set iterations = DialogSheets("Dialog1").EditBoxes(1)
>     Sheets("Assumptions Sheet").Activate
>     Range("A13").Value = iterations
> End Sub

My problem is that the DialogSheets object does not support the Value property -- how can I get around this?

With kind regards,

Olivier
0
Comment
Question by:ODefaux
  • 4
  • 3
7 Comments
 
LVL 3

Expert Comment

by:ChrisLewis
ID: 1431494
I believe what you want to do is use the  .TEXT property to return the value of a edit box.  

Similar to what you have written above, bu use the following:

Set iterations = DialogSheets("Dialog1").EditBoxes(1).text
Sheets("Assumptions Sheet").Activate
Range("A13").Value = iterations

Hope this helps.

CHris
0
 

Author Comment

by:ODefaux
ID: 1431495
The text property yields run-time error '424' -- object required.
0
 

Author Comment

by:ODefaux
ID: 1431496
The text property returns a run-time error 424 -- object required.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Expert Comment

by:ChrisLewis
ID: 1431497
First of all, what line is the error being generated on.  

Second, if you go into debug mode at the SET ITERATIONS line, can you print what DialogSheets("Dialog1").EditBoxes(1).Text is.

Third, what do you have iterations declared as.  And are you using Option Explicit.  

Fourth, try
Range("A13").Value = DialogSheets("Dialog1").EditBoxes(1).text
instead of using the set variable.

Most likely, there's something funky with how you are declaring Iterations.  Sorry I didn't pick this up, but the SET keyword should have tipped me.  Set is used for objects, and you should be just using a straight assignment.

I use this code all over the place in a system with 5 dialogs with 10 fields each.  I haven't ever had a problem with this, but I use the RANGE() = Dialog,Editbox.text without the intermediate variable.


DIM iterations as STRING
iterations = DialogSheets("Dialog1").EditBoxes(1).text
Sheets("Assumptions Sheet").Activate
Range("A13").Value = iterations

Hope this helps,

Chris
0
 

Author Comment

by:ODefaux
ID: 1431498
Dear Chris,

In answer to your questions

The error is being generated on the "Set iterations" line -- run-time error 424 (object required).

I can 't print this line as I am currently not hooked up to a printer.

I am using Option Explicit and have iterations dimensioned as a Variant (only thing that doesn't seem to generate an error).

I tried setting Range("A13").Value = DialogSheets("Dialog1").EditBoxes(1).text just to see and it didn't work (run-time error 1004 -- Range method of Application class failed).  I actually need the iterations variable in my code to dimension a simulation I am doing, so even if it had worked, I still would have had to find a way to assign the Edit Box input to a variable name.  Perhaps one way would be to place the value in a range on the spreadsheet and then assign it a variable name?

Hope this clarifies somewhat.  I've been stuck on this for weeks.

Thanks again for your help. Sorry I have to keep rejecting your answer until I get closer to making this thing work.

Olivier

0
 
LVL 3

Accepted Solution

by:
ChrisLewis earned 130 total points
ID: 1431499
Olivier

By print, I mean to type this in the debug window:

? DialogSheets("Dialog1").EditBoxes(1).text

and press enter.  Put a break point on the line in code to bring up the debug window.  You should get what's in the edit control.

For the iterations, use a straight assignment, not the Set command. Set can only be used for objects, not for variables.

iterations = DialogSheets("Dialog1").EditBoxes(1).text

Your error 1004 has to do with your range, not with the dialog box, so look at how you are assigning your range.  Make sure that the sheet the range is on is active.

Hope this helps,

Chris


0
 

Author Comment

by:ODefaux
ID: 1431500
The straight assignment did the trick (no set command)!  Thanks for the assistance and persistence.

Olivier
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Controlling which port to download from 4 71
DIR issue 7 51
SSRS expression Issue finding a string 10 66
Convert VB6 MSXML2.ServerXMLHTTP process to C# 2 41
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now