Excel/VBA Coding Help

  • Thread starter Thread starter JoeMathews
  • Start date Start date
J

JoeMathews

Forgive the stupidity of the question, but I am an experienced Excel user
trying VBA for user forms for the first time.

I have an Excel spreadsheet that is a 2 column Cost Savings Calculator. It
compares costs between two different products. The formula is fairly complex,
so I created a VBA user form.

My problem is that it looks great, but I can't figure out the coding. I
basically need the Excel cells to populate the corresponding VBA form fields.
 
It depends on the object you are using, but you could iterate over cell until
you find a break point (let's say an empty cell) and put values inside the
control (usually control.add or addItem).
To iterate over rows:
i=0;
while (Range(cellStart).Offset(i,0) <>"")
control.add (Range(cellStart).Offset(i,0).value) 'check the right way!
wend
 
I would name the ranges containing the target values, then when the form initilizes:

Private Sub UserForm_Initialize()
Me.TextBox1.Value = Application.Range("firstnamedrange").Value

End Sub

This ties the textbox1 to the value in the named range.

Hope this helps.
Tom



JoeMathews wrote:

Excel/VBA Coding Help
13-Nov-09

Forgive the stupidity of the question, but I am an experienced Excel use
trying VBA for user forms for the first time

I have an Excel spreadsheet that is a 2 column Cost Savings Calculator. I
compares costs between two different products. The formula is fairly complex
so I created a VBA user form

My problem is that it looks great, but I cannot figure out the coding.
basically need the Excel cells to populate the corresponding VBA form fields.

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Test-Driven Development in Microsoft .NET
http://www.eggheadcafe.com/tutorial...e0-a14465096ab0/testdriven-development-i.aspx
 
JoeMathews,

I'm not sure how a two product cost comparison would yield a complex formula
that requires the use of a UserForm, but nonetheless, there needs to be more
detail behind what you are trying to accomplish. Some sample questions that
I'm sure most people will be wondering about are below.

What types of controls does your UserForm contain? Is it something where
the user selects an item from ComboBox1, selects an item from ComboBox2, and
then clicks a CommandButton that executes the formula? Whenever a result is
created, where on the spreadsheet do you want it to end up? Do you want a
TextBox value and ComboBox value to be inserted onto the spreadsheet and if
so where on the spreadsheet do you want those values inserted? Are the
UserForm inputs necessary to the formula calculation, and if so how are the
inputs related? (I think you get the point regarding how to be more
detailed).

It general, the more specific you are, the better posters will be able to
assist you. Also, if you have VBA code, post it along with where you are
experiencing problems (or have questions, difficulties, etc.) with the code.
It's nice to know that your UserForm looks great, but your description leaves
everyone wondering what's going on with the guts behind the UserForm.

Best,

Matthew Herbert
 
JoeMathews,

I initial read your post incorrectly, however, the questions regarding the
type of control(s) you want filled is very relevant in this case. If your
control is a ListBox or ComboBox, look up .List (which can be an array
reference), .RowSource (which can be a spreadsheet location reference),
..AddItem (which can be a single addition) in the help section.

Best,

Matt
 
Back
Top