Spinbutton Linked to Textbox
Tom,
I really appreciate your assitance, but that solution
isnt working. It appears that in the cases where the
textbox and the spin button are linked to a cell through
the control source setting, the listboxs update in
realtime as the spinbuttons are depressed. For those
textbox that are displaying formatted text, (either
percentages or currency), linking the control source
seems to dissallow the formatting, and isnt possible for
those SB's where a single spinbutton impacts 2
textboxes. So, those textbox spinbutton combos do not
update realtime even with the update rowsource command
included, and adding a calculate command. Do you have
any other ideas on how to make this work.
Thanks again for your assitance.
Here is the code as it currently exists.
Dave
Sub updateRowSource()
With Overview
..ListBox1.RowSource = "ranking!list1"
..ListBox2.RowSource = "ranking!list2"
..ListBox3.RowSource = "ranking!list3"
..ListBox4.RowSource = "ranking!list4"
..ListBox5.RowSource = "ranking!list5"
..ListBox6.RowSource = "ranking!list6"
..ListBox7.RowSource = "ranking!list7"
End With
End Sub
Private Sub Userform_Initialize()
ListBox1.RowSource = "ranking!list1"
ListBox1.ColumnWidths = "28;120;30"
ListBox2.RowSource = "ranking!list2"
ListBox2.ColumnWidths = "28;120;30"
ListBox3.RowSource = "ranking!list3"
ListBox3.ColumnWidths = "28;120;30"
ListBox4.RowSource = "ranking!list4"
ListBox4.ColumnWidths = "28;120;30"
ListBox5.RowSource = "ranking!list5"
ListBox5.ColumnWidths = "28;120;30"
ListBox6.RowSource = "ranking!list6"
ListBox6.ColumnWidths = "28;120;30"
ListBox7.RowSource = "ranking!list7"
ListBox7.ColumnWidths = "28;120;30"
TextBox3.Value = Format(Worksheets("ua").Range
("d11").Value, "##,##0%")
TextBox6.Value = Format(Worksheets("ua").Range
("d12").Value, "##,##0%")
TextBox7.Value = Format(Worksheets("ua").Range
("d18").Value, "##,##0.00")
TextBox8.Value = Format(Me.SpinButton7.Value /
100, "##,##0%")
TextBox9.Value = Format(Me.SpinButton8.Value /
100, "##,##0%")
TextBox10.Value = Format(Me.SpinButton9.Value /
100, "##,##0%")
TextBox12.Value = Format(Worksheets("ua").Range
("n25").Value / 100, "##,##0.00%")
End Sub
Private Sub CommandButton1_Click()
Unload UAssump
End Sub
Private Sub SpinButton1_Change()
TextBox1.Text = SpinButton1.Value
Calculate
updateRowSource
End Sub
Private Sub SpinButton2_Change()
TextBox2.Text = SpinButton2.Value
Calculate
updateRowSource
End Sub
Private Sub SpinButton3_change()
Me.TextBox3.Text = Format(Me.SpinButton3.Value /
100, "##,##0%")
Me.TextBox6.Text = Format(1 - (Me.SpinButton3.Value /
100), "##,##0%")
Calculate
updateRowSource
End Sub
Private Sub SpinButton4_Change()
TextBox4.Text = SpinButton4.Value
Calculate
updateRowSource
End Sub
Private Sub SpinButton5_Change()
TextBox5.Text = SpinButton5.Value
Calculate
updateRowSource
End Sub
Private Sub SpinButton6_Change()
Me.TextBox7.Text = Me.SpinButton6.Value
Me.TextBox7.Text = Format
(Me.SpinButton6.Value, "##,##0.00")
Calculate
updateRowSource
End Sub
Private Sub SpinButton7_change()
Me.TextBox8.Text = Format(Me.SpinButton7.Value /
100, "##,##0%")
Me.TextBox12.Value = Format((((Me.SpinButton7.Value /
100) * 0.6) + ((Me.SpinButton8.Value _
/ 100) * 0.2) + ((Me.SpinButton9.Value / 100) *
0.09)), "##,##0.00%")
Calculate
updateRowSource
End Sub
Private Sub SpinButton8_change()
Me.TextBox9.Text = Format(Me.SpinButton8.Value /
100, "##,##0%")
Me.TextBox12.Value = Format((((Me.SpinButton7.Value /
100) * 0.6) + ((Me.SpinButton8.Value _
/ 100) * 0.2) + ((Me.SpinButton9.Value / 100) *
0.09)), "##,##0.00%")
Calculate
updateRowSource
End Sub
Private Sub SpinButton9_change()
Me.TextBox10.Text = Format(Me.SpinButton9.Value /
100, "##,##0%")
Me.TextBox12.Value = Format((((Me.SpinButton7.Value /
100) * 0.6) + ((Me.SpinButton8.Value _
/ 100) * 0.2) + ((Me.SpinButton9.Value / 100) *
0.09)), "##,##0.00%")
Calculate
updateRowSource
End Sub
-----Original Message-----
If having the worksheet recalculate doesn't cause your
listboxes to update,
then I guess you would write a routine that reassigns
the rowsource of all
the listboxes and call that routine from the change
event of each
spinbutton.
sub updateRowSource()
With Userform1
..ListBox1.RowSource = "ranking!list1"
..ListBox2.RowSource = "ranking!list2"
..ListBox3.RowSource = "ranking!list3"
..ListBox4.RowSource = "ranking!list4"
..ListBox5.RowSource = "ranking!list5"
..ListBox6.RowSource = "ranking!list6"
..ListBox7.RowSource = "ranking!list7"
End With
End Sub
Private Sub SpinButton3_change()
Me.TextBox3.Text = Format(Me.SpinButton3.Value /
100, "##,##0%")
Me.TextBox6.Text = Format(1 - (Me.SpinButton3.Value /
100), "##,##0%")
updateRowSource
End Sub
--
Regards,
Tom Ogilvy
"David Reid" wrote in message
...
I have multiple listboxs that are updated as the
spreadsheet calculates, which indicates changes made
using spinbuttons of multiple variables. It is not
clear
how I would implement the ssource suggestion giving my
situation.
Private Sub Userform_Initialize()
ListBox1.RowSource = "ranking!list1"
ListBox1.ColumnWidths = "28;120;30"
ListBox2.RowSource = "ranking!list2"
ListBox2.ColumnWidths = "28;120;30"
ListBox3.RowSource = "ranking!list3"
ListBox3.ColumnWidths = "28;120;30"
ListBox4.RowSource = "ranking!list4"
ListBox4.ColumnWidths = "28;120;30"
ListBox5.RowSource = "ranking!list5"
ListBox5.ColumnWidths = "28;120;30"
ListBox6.RowSource = "ranking!list6"
ListBox6.ColumnWidths = "28;120;30"
ListBox7.RowSource = "ranking!list7"
ListBox7.ColumnWidths = "28;120;30"
TextBox3.Value = Format(Worksheets("ua").Range
("d11").Value, "##,##0%")
TextBox6.Value = Format(Worksheets("ua").Range
("d12").Value, "##,##0%")
TextBox7.Value = Format(Worksheets("ua").Range
("d18").Value, "##,##0.00")
TextBox8.Value = Format(Me.SpinButton7.Value /
100, "##,##0%")
TextBox9.Value = Format(Me.SpinButton8.Value /
100, "##,##0%")
TextBox10.Value = Format(Me.SpinButton9.Value /
100, "##,##0%")
TextBox12.Value = Format(Worksheets("ua").Range
("n25").Value / 100, "##,##0.00%")
End Sub
Private Sub CommandButton1_Click()
Unload UAssump
End Sub
Private Sub SpinButton1_Change()
TextBox1.Text = SpinButton1.Value
End Sub
Private Sub SpinButton2_Change()
TextBox2.Text = SpinButton2.Value
End Sub
Private Sub SpinButton3_change()
Me.TextBox3.Text = Format(Me.SpinButton3.Value /
100, "##,##0%")
Me.TextBox6.Text = Format(1 - (Me.SpinButton3.Value /
100), "##,##0%")
End Sub
Private Sub SpinButton4_Change()
TextBox4.Text = SpinButton4.Value
End Sub
Private Sub SpinButton5_Change()
TextBox5.Text = SpinButton5.Value
End Sub
Private Sub SpinButton6_Change()
Me.TextBox7.Text = Me.SpinButton6.Value
Me.TextBox7.Text = Format
(Me.SpinButton6.Value, "##,##0.00")
End Sub
Private Sub SpinButton7_change()
Me.TextBox8.Text = Format(Me.SpinButton7.Value /
100, "##,##0%")
Me.TextBox12.Value = Format((((Me.SpinButton7.Value /
100) * 0.6) + ((Me.SpinButton8.Value _
/ 100) * 0.2) + ((Me.SpinButton9.Value / 100) *
0.09)), "##,##0.00%")
End Sub
Private Sub SpinButton8_change()
Me.TextBox9.Text = Format(Me.SpinButton8.Value /
100, "##,##0%")
Me.TextBox12.Value = Format((((Me.SpinButton7.Value /
100) * 0.6) + ((Me.SpinButton8.Value _
/ 100) * 0.2) + ((Me.SpinButton9.Value / 100) *
0.09)), "##,##0.00%")
End Sub
Private Sub SpinButton9_change()
Me.TextBox10.Text = Format(Me.SpinButton9.Value /
100, "##,##0%")
Me.TextBox12.Value = Format((((Me.SpinButton7.Value /
100) * 0.6) + ((Me.SpinButton8.Value _
/ 100) * 0.2) + ((Me.SpinButton9.Value / 100) *
0.09)), "##,##0.00%")
End Sub
-----Original Message-----
Private Sub SpinButton7_change()
Me.TextBox8.Text = Format(Me.SpinButton7.Value /
100, "##,##0%")
Me.TextBox12.Value = Format((((Me.SpinButton7.Value /
100) * 0.6) + ((Me.SpinButton8.Value _
/ 100) * 0.2) + ((Me.SpinButton9.Value / 100) *
0.09)), "##,##0.00%")
sSource = Listbox1.RowSource
Listbox1.RowSource = sSource
End Sub
--
Regards,
Tom Ogilvy
"David Reid" wrote in message
...
I have a spreadsheet using userforms. On the
userform,
there are several spinbuttons linked to a textboxs.
The
textboxs are formatted to show the values as either
percentages or dollar values. When the user presses
the
spinbutton, the values change in the textbox which
adjusts a calculation that takes place in the
spreadsheet. The results of that change appear in a
list
box that is located on the same userform. The way
it
is
working now, the spinbutton value change does not
change
the results calculation shown in the list box until
the
form is closed and reopened or different spinbutton
is
depressed. Controlsource of the spinbutton is set
to a
cell on the spreadsheet, which changes each time the
spinbutton is depressed. Now I just want the
spreadsheet
to calculate and display the results immediately in
the
associated list box. I am a programming rookie, so
a
simple explanation on why it isnt happening, and
what I
need to do to make it work would be greatly
appreciated.
Below is an example of the code from one of the spin
buttons.
Private Sub SpinButton7_change()
Me.TextBox8.Text = Format(Me.SpinButton7.Value /
100, "##,##0%")
Me.TextBox12.Value = Format
((((Me.SpinButton7.Value /
100) * 0.6) + ((Me.SpinButton8.Value _
/ 100) * 0.2) + ((Me.SpinButton9.Value / 100) *
0.09)), "##,##0.00%")
End Sub
Thanks for the assistance
Dave
.
.
|