B
bruce forster
I have a userform , with comboxes, which is coded to change textbox when combobox is changed. Unfortunately, when I hit the tab to move to the next box the program locks up. Any suggestions??
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jake Marx
Hi Bruce,
Yes - post the code behind your UserForm so we can help you troubleshoot it.
<g>
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
bruce said:I have a userform , with comboxes, which is coded to change textbox
when combobox is changed. Unfortunately, when I hit the tab to move
to the next box the program locks up. Any suggestions??
Yes - post the code behind your UserForm so we can help you troubleshoot it.
<g>
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
B
bruce forster
Here is a sample of the code for one combo box.
Private Sub ComboBox19_AfterUpdate(
TextBox129.Value = Sheets("BalShtAssump").Range("C20"
If ComboBox19.Value = "% of Revenue" Then TextBox129.Text = Format(TextBox129.Text, "0.00%"
If ComboBox19.Value = "Input" Then TextBox129.Text = Format(TextBox129.Text, "$#,##0"
If ComboBox19.Value = "% Change from Previous Year" Then TextBox129.Text = Format(TextBox129.Text, "0.00%"
If ComboBox19.Value = "$ Change from Previous Year" Then TextBox129.Text = Format(TextBox129.Text, "$#,##0"
TextBox130.Value = Sheets("BalShtAssump").Range("D20"
If ComboBox19.Value = "% of Revenue" Then TextBox130.Text = Format(TextBox130.Text, "0.00%"
If ComboBox19.Value = "Input" Then TextBox130.Text = Format(TextBox130.Text, "$#,##0"
If ComboBox19.Value = "% Change from Previous Year" Then TextBox130.Text = Format(TextBox130.Text, "0.00%"
If ComboBox19.Value = "$ Change from Previous Year" Then TextBox130.Text = Format(TextBox130.Text, "$#,##0"
TextBox131.Value = Sheets("BalShtAssump").Range("E20"
If ComboBox19.Value = "% of Revenue" Then TextBox131.Text = Format(TextBox131.Text, "0.00%"
If ComboBox19.Value = "Input" Then TextBox131.Text = Format(TextBox131.Text, "$#,##0"
If ComboBox19.Value = "% Change from Previous Year" Then TextBox131.Text = Format(TextBox131.Text, "0.00%"
If ComboBox19.Value = "$ Change from Previous Year" Then TextBox131.Text = Format(TextBox131.Text, "$#,##0"
TextBox132.Value = "
TextBox133.Value = "
TextBox134.Value = "
End Su
any suggestions would be helpful. I am schedule to present my program on Friday morning.
Private Sub ComboBox19_AfterUpdate(
TextBox129.Value = Sheets("BalShtAssump").Range("C20"
If ComboBox19.Value = "% of Revenue" Then TextBox129.Text = Format(TextBox129.Text, "0.00%"
If ComboBox19.Value = "Input" Then TextBox129.Text = Format(TextBox129.Text, "$#,##0"
If ComboBox19.Value = "% Change from Previous Year" Then TextBox129.Text = Format(TextBox129.Text, "0.00%"
If ComboBox19.Value = "$ Change from Previous Year" Then TextBox129.Text = Format(TextBox129.Text, "$#,##0"
TextBox130.Value = Sheets("BalShtAssump").Range("D20"
If ComboBox19.Value = "% of Revenue" Then TextBox130.Text = Format(TextBox130.Text, "0.00%"
If ComboBox19.Value = "Input" Then TextBox130.Text = Format(TextBox130.Text, "$#,##0"
If ComboBox19.Value = "% Change from Previous Year" Then TextBox130.Text = Format(TextBox130.Text, "0.00%"
If ComboBox19.Value = "$ Change from Previous Year" Then TextBox130.Text = Format(TextBox130.Text, "$#,##0"
TextBox131.Value = Sheets("BalShtAssump").Range("E20"
If ComboBox19.Value = "% of Revenue" Then TextBox131.Text = Format(TextBox131.Text, "0.00%"
If ComboBox19.Value = "Input" Then TextBox131.Text = Format(TextBox131.Text, "$#,##0"
If ComboBox19.Value = "% Change from Previous Year" Then TextBox131.Text = Format(TextBox131.Text, "0.00%"
If ComboBox19.Value = "$ Change from Previous Year" Then TextBox131.Text = Format(TextBox131.Text, "$#,##0"
TextBox132.Value = "
TextBox133.Value = "
TextBox134.Value = "
End Su
any suggestions would be helpful. I am schedule to present my program on Friday morning.
J
Jake Marx
Hi Bruce,
Nothing here looks like it should lock up Excel. Do you have any code in
the Change events of any of the TextBoxes you're modifying with the code
below (eg, TextBox129, TextBox130, TextBox131)? If so, you could be
inadvertently getting yourself into a loop. When you change the value of a
TextBox via code, it will fire the TextBox's Change event, which, if it in
turn changes another TextBox or ComboBox, can cause some problems.
What happens when you put a breakpoint on the first line of the
ComboBox19_AfterUpdate subroutine and step through your code (F8)? Do you
see it bouncing back and forth between different event subroutines?
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
Nothing here looks like it should lock up Excel. Do you have any code in
the Change events of any of the TextBoxes you're modifying with the code
below (eg, TextBox129, TextBox130, TextBox131)? If so, you could be
inadvertently getting yourself into a loop. When you change the value of a
TextBox via code, it will fire the TextBox's Change event, which, if it in
turn changes another TextBox or ComboBox, can cause some problems.
What happens when you put a breakpoint on the first line of the
ComboBox19_AfterUpdate subroutine and step through your code (F8)? Do you
see it bouncing back and forth between different event subroutines?
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
B
bruce forster
Hello Jake. Your last advice solved my problem so I thought I might throw one more question at you.
I am a VBA newbie and have not been using DIM in my code so it is getting rather large. In reviewing the code below can you give me some advice on how to code DIM so that i might be able to have my program run faster. Thanks.
I am a VBA newbie and have not been using DIM in my code so it is getting rather large. In reviewing the code below can you give me some advice on how to code DIM so that i might be able to have my program run faster. Thanks.
J
Jake Marx
Hi Bruce,
I'll give it a shot and try not to be too long-winded. <g> When declaring
variables with Dim, you should think about a few things. First, scope.
Scope is the context and lifetime of the variable. If you declare a
variable inside a Sub or Function, the lifetime of the variable will span
that Sub or Function only (ie, can't exist outside the routine). If you
declare a variable at the top of a standard module using Private (module
level), then the variable will be available to all code within that module.
If you declare a variable at the top of a standard module using Public
(global level), all modules in the project will have access to the variable.
You want to try to limit the scope of a variable as much as possible. So if
you only need a variable inside one procedure, then Dim it inside that
procedure instead of at module or global level.
Second, you should use explicit data types where possible. Instead of using
As Variant or As Object, you should be as explicit as possible when
declaring variables. If you are going to store a text value, then use a
String. If you are going to store a small whole number, then use Integer.
Longer whole numbers should use Long. There are some situations where you
want to use Variant or Object because you're not sure of the data type ahead
of time or need more flexibility, but those instances are not as common.
I'll take the first part of code from your earlier post as an example:
Dim sngVal As Single
sngVal = Sheets("BalShtAssump").Range("C20").Value
Select Case ComboBox19.Value
Case "% of Revenue", "% Change from Previous Year"
TextBox129.Text = Format$(sngVal, "0.00%")
Case "Input", "$ Change from Previous Year"
TextBox129.Text = Format$(sngVal, "$#,##0")
End Select
Since we're going to use the value of BalShtAssump!C20 more than once, it's
a good idea to store its value to a variable. In this case, you may want to
use a Single to store the value. Now that we have declared sngVal in the
subroutine, we can use the value anywhere in that same subroutine.
A Case block is a good way to avoid lots of If Then ElseIf Elses. In this
case, there are only 2 outcomes, so we can double up on the conditional
statements. The first line simply says let's look at the value of
ComboBox19. Each Case line that follows is comparing that value to other
values. So if the value is either "% of Revenue" or "% Change from Previous
Year", the code within that Case will execute (in this case, the value is
formatted as a percentage and written to TextBox129. If the value is not
matched, execution moves on to the next Case, and so on.
You may notice that I used Format$ instead of Format. Format$ is
syntactially equivalent to Format, but Format$ returns a String whereas
Format returns a string value that has been coerced into a Variant. Strings
stored in Variants are less efficient than Strings stored in Strings, so
Format$ is in turn more efficient. The $ can be used with most functions
that return a text value (eg, Left$, Right$, Mid$, Dir$, etc).
Oh, one other point. You should always use "Option Explicit" at the top of
every code module. This will force you to declare all of your variables -
the code will not compile unless you do. This keeps you from making silly
spelling mistakes or forgetting to Dim a variable. If you want to do this
all the time, go to Tools | Options in the VBE and check "Require Variable
Declaration" on the Editor tab.
Besides looking in these forums, you could buy a book or 2 to help you with
your VBA development. Here's a good list of books:
http://www.contextures.com/xlbooks.html
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
bruce said:I am a VBA newbie and have not been using DIM in my code so it is
getting rather large. In reviewing the code below can you give me
some advice on how to code DIM so that i might be able to have my
program run faster. Thanks.
I'll give it a shot and try not to be too long-winded. <g> When declaring
variables with Dim, you should think about a few things. First, scope.
Scope is the context and lifetime of the variable. If you declare a
variable inside a Sub or Function, the lifetime of the variable will span
that Sub or Function only (ie, can't exist outside the routine). If you
declare a variable at the top of a standard module using Private (module
level), then the variable will be available to all code within that module.
If you declare a variable at the top of a standard module using Public
(global level), all modules in the project will have access to the variable.
You want to try to limit the scope of a variable as much as possible. So if
you only need a variable inside one procedure, then Dim it inside that
procedure instead of at module or global level.
Second, you should use explicit data types where possible. Instead of using
As Variant or As Object, you should be as explicit as possible when
declaring variables. If you are going to store a text value, then use a
String. If you are going to store a small whole number, then use Integer.
Longer whole numbers should use Long. There are some situations where you
want to use Variant or Object because you're not sure of the data type ahead
of time or need more flexibility, but those instances are not as common.
I'll take the first part of code from your earlier post as an example:
Dim sngVal As Single
sngVal = Sheets("BalShtAssump").Range("C20").Value
Select Case ComboBox19.Value
Case "% of Revenue", "% Change from Previous Year"
TextBox129.Text = Format$(sngVal, "0.00%")
Case "Input", "$ Change from Previous Year"
TextBox129.Text = Format$(sngVal, "$#,##0")
End Select
Since we're going to use the value of BalShtAssump!C20 more than once, it's
a good idea to store its value to a variable. In this case, you may want to
use a Single to store the value. Now that we have declared sngVal in the
subroutine, we can use the value anywhere in that same subroutine.
A Case block is a good way to avoid lots of If Then ElseIf Elses. In this
case, there are only 2 outcomes, so we can double up on the conditional
statements. The first line simply says let's look at the value of
ComboBox19. Each Case line that follows is comparing that value to other
values. So if the value is either "% of Revenue" or "% Change from Previous
Year", the code within that Case will execute (in this case, the value is
formatted as a percentage and written to TextBox129. If the value is not
matched, execution moves on to the next Case, and so on.
You may notice that I used Format$ instead of Format. Format$ is
syntactially equivalent to Format, but Format$ returns a String whereas
Format returns a string value that has been coerced into a Variant. Strings
stored in Variants are less efficient than Strings stored in Strings, so
Format$ is in turn more efficient. The $ can be used with most functions
that return a text value (eg, Left$, Right$, Mid$, Dir$, etc).
Oh, one other point. You should always use "Option Explicit" at the top of
every code module. This will force you to declare all of your variables -
the code will not compile unless you do. This keeps you from making silly
spelling mistakes or forgetting to Dim a variable. If you want to do this
all the time, go to Tools | Options in the VBE and check "Require Variable
Declaration" on the Editor tab.
Besides looking in these forums, you could buy a book or 2 to help you with
your VBA development. Here's a good list of books:
http://www.contextures.com/xlbooks.html
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
B
bruce forster
Jake
I just got back and have read your response. Thanks for the length explanation.
I just got back and have read your response. Thanks for the length explanation.