Spinbutton Linked to Textbox

[David Reid]

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

 

[Tom Ogilvy]

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

 

[David Reid]

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