Use a form in Excel to update a Macro's content..
I have an Excel form with a textbox, 2 checkboxs (One Yes, one No) and a submit button.
When a user uses the form and clicks the Yes button and submit I want a statement added to my Sub Macro1().
This is what's in the Macro
VB Code:
For Each MyCell2 In Sheet1.Columns("A").Cells
With MyCell2
If .Value = "" Then Exit For
If .Value = 574 _
[COLOR=Red]Or .Value = 555 _[/COLOR]
Or .Value = 510 Then
.Offset(0, 12).Value = .Offset(0, 8).Value * 1.1
End If
End With
Next MyCell2
This is the statement I want added beneath the section in red:
Or . Value = "WHATS IN THE TEXTBOX" _
Is this possible? I have a user that doesn't know any code and trying to make it easy for them to add more values to the If statement once they encounter ones that need to be added.. Thanks!
Re: Use a form in Excel to update a Macro's content..
Let me try to rephrase your problem:
you want the code shown be executed when the user clicks submit with the option "NO" checked.
and yoou want the code plus the other line executed when the users clicks submit with the "YES" checked.
If that's correct a simple if..then aound the other code-line, checking for Yes or No checked would do it.
if it's not correct, please explain a bit more!
Re: Use a form in Excel to update a Macro's content..
I think what he's trying to say is that when no is checked, the code will run as shown.
When yes is checked, whatever value is in the text box will become a new PERMANENT condition in the if statement. So if the user enters the value 590 in the textbox with yes checked, it will check for 590 this time and every other time the macro is run in the future.
Did I get that right?
If so, having your code append its own conditions seems like more trouble than its worth. You'd be better suited to make a table in a hidden sheet somewhere with all the lookup values and have your form add the new value to that table. Or you could use a named range that holds a comma separated string of all your lookup values. Or you could have the lookup values read from a text file, or... you get the drift.
If you're hellbent on having the conditions added permanently in the code for some reason, its definitely possible.
Search for "Programming the VBE" in this forum or Google. There are several ways to go about doing it, none of them very practical for your purposes.
Re: Use a form in Excel to update a Macro's content..
Quote:
Originally Posted by mikeyc1204
I think what he's trying to say is that when no is checked, the code will run as shown.
When yes is checked, whatever value is in the text box will become a new PERMANENT condition in the if statement. So if the user enters the value 590 in the textbox with yes checked, it will check for 590 this time and every other time the macro is run in the future.
Did I get that right?
If so, having your code append its own conditions seems like more trouble than its worth. You'd be better suited to make a table in a hidden sheet somewhere with all the lookup values and have your form add the new value to that table. Or you could use a named range that holds a comma separated string of all your lookup values. Or you could have the lookup values read from a text file, or... you get the drift.
If you're hellbent on having the conditions added permanently in the code for some reason, its definitely possible.
Search for "Programming the VBE" in this forum or Google. There are several ways to go about doing it, none of them very practical for your purposes.
Yes you are right, but since I'm a noob I will try to do the tables thing.
