Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spinbutton Linked to Textbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spinbutton Linked to Textbox
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TextBox linked to cell????? | Excel Discussion (Misc queries) | |||
Textbox linked to a cell (reposted) | Excel Discussion (Misc queries) | |||
Textbox linked to a cell | Excel Discussion (Misc queries) | |||
ComboBox linked to TextBox | Excel Discussion (Misc queries) | |||
TextBox linked to a ComboBox? | Excel Discussion (Misc queries) |