|
Active X not updating properties on Excel Sheet
I have some option buttons that I change the caption,
disable or enable based on a certain sheets value in a workbook. On the activate event I reset the properties, enable or disable. The problem is - the values are not changing on the screen. I attempted to refresh the workbook, and they still do not change the values. When I put the active x controls into design mode, the values are how I set them in code, i.e., correct. But - the screen doesn't display it correctly. Can anyone tell me what I am doing wrong?? I have inserted my code: Private Sub Worksheet_Activate() CleanUpRadioButtons SetDefaultRadio ThisWorkbook.RefreshAll Application.ScreenUpdating = True End Sub Sub CleanUpRadioButtons() With ActiveSheet .OptionButton1.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d9").Value), 1, 1) .OptionButton2.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d10").Value), 1, 1) .OptionButton3.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d11").Value), 1, 1) .OptionButton4.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d12").Value), 1, 1) .OptionButton5.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d14").Value), 1, 1) .OptionButton6.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d15").Value), 1, 1) .OptionButton7.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d16").Value), 1, 1) .OptionButton8.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d17").Value), 1, 1) .OptionButton9.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d18").Value), 1, 1) .OptionButton10.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d19").Value), 1, 1) .OptionButton11.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d20").Value), 1, 1) .OptionButton12.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d21").Value), 1, 1) .OptionButton13.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d23").Value), 1, 1) .OptionButton14.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d24").Value), 1, 1) End With If ActiveSheet.Name = "VistaMax_Input" Or ActiveSheet.Name = "VistaMax_Output" Then EnableorDisable Else SetUpLogic End If End Sub Sub EnableorDisable() With ActiveSheet If .OptionButton1.Caption = "A" Or .OptionButton1.Caption = "D" Then .OptionButton1.Enabled = True Else .OptionButton1.Enabled = False End If If .OptionButton2.Caption = "A" Or .OptionButton2.Caption = "D" Then .OptionButton2.Enabled = True Else .OptionButton2.Enabled = False End If If .OptionButton3.Caption = "A" Or .OptionButton3.Caption = "D" Then .OptionButton3.Enabled = True Else .OptionButton3.Enabled = False End If If .OptionButton4.Caption = "A" Or .OptionButton4.Caption = "D" Then .OptionButton4.Enabled = True Else .OptionButton4.Enabled = False End If If .OptionButton5.Caption = "A" Or .OptionButton5.Caption = "D" Then .OptionButton5.Enabled = True Else .OptionButton5.Enabled = False End If If .OptionButton6.Caption = "A" Or .OptionButton6.Caption = "D" Then .OptionButton6.Enabled = True Else .OptionButton6.Enabled = False End If If .OptionButton7.Caption = "A" Or .OptionButton7.Caption = "D" Then .OptionButton7.Enabled = True Else .OptionButton7.Enabled = False End If If .OptionButton8.Caption = "A" Or .OptionButton8.Caption = "D" Then .OptionButton8.Enabled = True Else .OptionButton8.Enabled = False End If If .OptionButton9.Caption = "A" Or .OptionButton9.Caption = "D" Then .OptionButton9.Enabled = True Else .OptionButton9.Enabled = False End If If .OptionButton10.Caption = "A" Or .OptionButton10.Caption = "D" Then .OptionButton10.Enabled = True Else .OptionButton10.Enabled = False End If If .OptionButton11.Caption = "A" Or .OptionButton11.Caption = "D" Then .OptionButton11.Enabled = True Else .OptionButton11.Enabled = False End If If .OptionButton12.Caption = "A" Or .OptionButton12.Caption = "D" Then .OptionButton12.Enabled = True Else .OptionButton12.Enabled = False End If If .OptionButton13.Caption = "A" Or .OptionButton13.Caption = "D" Then .OptionButton13.Enabled = True Else .OptionButton13.Enabled = False End If If .OptionButton14.Caption = "A" Or .OptionButton14.Caption = "D" Then .OptionButton14.Enabled = True Else .OptionButton14.Enabled = False End If End With End Sub |
Active X not updating properties on Excel Sheet
Look into
Userform1.Repaint after all your changes. -- sb "Please Help" wrote in message ... I have some option buttons that I change the caption, disable or enable based on a certain sheets value in a workbook. On the activate event I reset the properties, enable or disable. The problem is - the values are not changing on the screen. I attempted to refresh the workbook, and they still do not change the values. When I put the active x controls into design mode, the values are how I set them in code, i.e., correct. But - the screen doesn't display it correctly. Can anyone tell me what I am doing wrong?? I have inserted my code: Private Sub Worksheet_Activate() CleanUpRadioButtons SetDefaultRadio ThisWorkbook.RefreshAll Application.ScreenUpdating = True End Sub Sub CleanUpRadioButtons() With ActiveSheet .OptionButton1.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d9").Value), 1, 1) .OptionButton2.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d10").Value), 1, 1) .OptionButton3.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d11").Value), 1, 1) .OptionButton4.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d12").Value), 1, 1) .OptionButton5.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d14").Value), 1, 1) .OptionButton6.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d15").Value), 1, 1) .OptionButton7.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d16").Value), 1, 1) .OptionButton8.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d17").Value), 1, 1) .OptionButton9.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d18").Value), 1, 1) .OptionButton10.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d19").Value), 1, 1) .OptionButton11.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d20").Value), 1, 1) .OptionButton12.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d21").Value), 1, 1) .OptionButton13.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d23").Value), 1, 1) .OptionButton14.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d24").Value), 1, 1) End With If ActiveSheet.Name = "VistaMax_Input" Or ActiveSheet.Name = "VistaMax_Output" Then EnableorDisable Else SetUpLogic End If End Sub Sub EnableorDisable() With ActiveSheet If .OptionButton1.Caption = "A" Or .OptionButton1.Caption = "D" Then .OptionButton1.Enabled = True Else .OptionButton1.Enabled = False End If If .OptionButton2.Caption = "A" Or .OptionButton2.Caption = "D" Then .OptionButton2.Enabled = True Else .OptionButton2.Enabled = False End If If .OptionButton3.Caption = "A" Or .OptionButton3.Caption = "D" Then .OptionButton3.Enabled = True Else .OptionButton3.Enabled = False End If If .OptionButton4.Caption = "A" Or .OptionButton4.Caption = "D" Then .OptionButton4.Enabled = True Else .OptionButton4.Enabled = False End If If .OptionButton5.Caption = "A" Or .OptionButton5.Caption = "D" Then .OptionButton5.Enabled = True Else .OptionButton5.Enabled = False End If If .OptionButton6.Caption = "A" Or .OptionButton6.Caption = "D" Then .OptionButton6.Enabled = True Else .OptionButton6.Enabled = False End If If .OptionButton7.Caption = "A" Or .OptionButton7.Caption = "D" Then .OptionButton7.Enabled = True Else .OptionButton7.Enabled = False End If If .OptionButton8.Caption = "A" Or .OptionButton8.Caption = "D" Then .OptionButton8.Enabled = True Else .OptionButton8.Enabled = False End If If .OptionButton9.Caption = "A" Or .OptionButton9.Caption = "D" Then .OptionButton9.Enabled = True Else .OptionButton9.Enabled = False End If If .OptionButton10.Caption = "A" Or .OptionButton10.Caption = "D" Then .OptionButton10.Enabled = True Else .OptionButton10.Enabled = False End If If .OptionButton11.Caption = "A" Or .OptionButton11.Caption = "D" Then .OptionButton11.Enabled = True Else .OptionButton11.Enabled = False End If If .OptionButton12.Caption = "A" Or .OptionButton12.Caption = "D" Then .OptionButton12.Enabled = True Else .OptionButton12.Enabled = False End If If .OptionButton13.Caption = "A" Or .OptionButton13.Caption = "D" Then .OptionButton13.Enabled = True Else .OptionButton13.Enabled = False End If If .OptionButton14.Caption = "A" Or .OptionButton14.Caption = "D" Then .OptionButton14.Enabled = True Else .OptionButton14.Enabled = False End If End With End Sub |
Active X not updating properties on Excel Sheet
Steve, thanks for the tip, but the repaint function is not
working. The option buttons are embedded into an Excel sheet. They are not linked to any cells, but they are not on a user form. I am thinking the only way to get around this is to create a form for the user button to be on, but I do not want to do that, as the excel sheet is the application, and the option buttons are userful the entire time the sheet is open. I feel as though there is something that I am missing. It is like a refresh or repaint thing, but nothing is working! Thanks for any help you may have on this -----Original Message----- Look into Userform1.Repaint after all your changes. -- sb "Please Help" wrote in message ... I have some option buttons that I change the caption, disable or enable based on a certain sheets value in a workbook. On the activate event I reset the properties, enable or disable. The problem is - the values are not changing on the screen. I attempted to refresh the workbook, and they still do not change the values. When I put the active x controls into design mode, the values are how I set them in code, i.e., correct. But - the screen doesn't display it correctly. Can anyone tell me what I am doing wrong?? I have inserted my code: Private Sub Worksheet_Activate() CleanUpRadioButtons SetDefaultRadio ThisWorkbook.RefreshAll Application.ScreenUpdating = True End Sub Sub CleanUpRadioButtons() With ActiveSheet .OptionButton1.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d9").Value), 1, 1) .OptionButton2.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d10").Value), 1, 1) .OptionButton3.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d11").Value), 1, 1) .OptionButton4.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d12").Value), 1, 1) .OptionButton5.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d14").Value), 1, 1) .OptionButton6.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d15").Value), 1, 1) .OptionButton7.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d16").Value), 1, 1) .OptionButton8.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d17").Value), 1, 1) .OptionButton9.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d18").Value), 1, 1) .OptionButton10.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d19").Value), 1, 1) .OptionButton11.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d20").Value), 1, 1) .OptionButton12.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d21").Value), 1, 1) .OptionButton13.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d23").Value), 1, 1) .OptionButton14.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d24").Value), 1, 1) End With If ActiveSheet.Name = "VistaMax_Input" Or ActiveSheet.Name = "VistaMax_Output" Then EnableorDisable Else SetUpLogic End If End Sub Sub EnableorDisable() With ActiveSheet If .OptionButton1.Caption = "A" Or .OptionButton1.Caption = "D" Then .OptionButton1.Enabled = True Else .OptionButton1.Enabled = False End If If .OptionButton2.Caption = "A" Or .OptionButton2.Caption = "D" Then .OptionButton2.Enabled = True Else .OptionButton2.Enabled = False End If If .OptionButton3.Caption = "A" Or .OptionButton3.Caption = "D" Then .OptionButton3.Enabled = True Else .OptionButton3.Enabled = False End If If .OptionButton4.Caption = "A" Or .OptionButton4.Caption = "D" Then .OptionButton4.Enabled = True Else .OptionButton4.Enabled = False End If If .OptionButton5.Caption = "A" Or .OptionButton5.Caption = "D" Then .OptionButton5.Enabled = True Else .OptionButton5.Enabled = False End If If .OptionButton6.Caption = "A" Or .OptionButton6.Caption = "D" Then .OptionButton6.Enabled = True Else .OptionButton6.Enabled = False End If If .OptionButton7.Caption = "A" Or .OptionButton7.Caption = "D" Then .OptionButton7.Enabled = True Else .OptionButton7.Enabled = False End If If .OptionButton8.Caption = "A" Or .OptionButton8.Caption = "D" Then .OptionButton8.Enabled = True Else .OptionButton8.Enabled = False End If If .OptionButton9.Caption = "A" Or .OptionButton9.Caption = "D" Then .OptionButton9.Enabled = True Else .OptionButton9.Enabled = False End If If .OptionButton10.Caption = "A" Or .OptionButton10.Caption = "D" Then .OptionButton10.Enabled = True Else .OptionButton10.Enabled = False End If If .OptionButton11.Caption = "A" Or .OptionButton11.Caption = "D" Then .OptionButton11.Enabled = True Else .OptionButton11.Enabled = False End If If .OptionButton12.Caption = "A" Or .OptionButton12.Caption = "D" Then .OptionButton12.Enabled = True Else .OptionButton12.Enabled = False End If If .OptionButton13.Caption = "A" Or .OptionButton13.Caption = "D" Then .OptionButton13.Enabled = True Else .OptionButton13.Enabled = False End If If .OptionButton14.Caption = "A" Or .OptionButton14.Caption = "D" Then .OptionButton14.Enabled = True Else .OptionButton14.Enabled = False End If End With End Sub . |
Active X not updating properties on Excel Sheet
The Repaint also works on the page object.
(I don't know the syntax, but try out a few things) Maybe Activesheet.Repaint Sheets("Sheet1").Repaint Check out VB Help on Repaint. -- sb "Please Help Again" wrote in message ... Steve, thanks for the tip, but the repaint function is not working. The option buttons are embedded into an Excel sheet. They are not linked to any cells, but they are not on a user form. I am thinking the only way to get around this is to create a form for the user button to be on, but I do not want to do that, as the excel sheet is the application, and the option buttons are userful the entire time the sheet is open. I feel as though there is something that I am missing. It is like a refresh or repaint thing, but nothing is working! Thanks for any help you may have on this -----Original Message----- Look into Userform1.Repaint after all your changes. -- sb "Please Help" wrote in message ... I have some option buttons that I change the caption, disable or enable based on a certain sheets value in a workbook. On the activate event I reset the properties, enable or disable. The problem is - the values are not changing on the screen. I attempted to refresh the workbook, and they still do not change the values. When I put the active x controls into design mode, the values are how I set them in code, i.e., correct. But - the screen doesn't display it correctly. Can anyone tell me what I am doing wrong?? I have inserted my code: Private Sub Worksheet_Activate() CleanUpRadioButtons SetDefaultRadio ThisWorkbook.RefreshAll Application.ScreenUpdating = True End Sub Sub CleanUpRadioButtons() With ActiveSheet .OptionButton1.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d9").Value), 1, 1) .OptionButton2.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d10").Value), 1, 1) .OptionButton3.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d11").Value), 1, 1) .OptionButton4.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d12").Value), 1, 1) .OptionButton5.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d14").Value), 1, 1) .OptionButton6.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d15").Value), 1, 1) .OptionButton7.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d16").Value), 1, 1) .OptionButton8.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d17").Value), 1, 1) .OptionButton9.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d18").Value), 1, 1) .OptionButton10.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d19").Value), 1, 1) .OptionButton11.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d20").Value), 1, 1) .OptionButton12.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d21").Value), 1, 1) .OptionButton13.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d23").Value), 1, 1) .OptionButton14.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d24").Value), 1, 1) End With If ActiveSheet.Name = "VistaMax_Input" Or ActiveSheet.Name = "VistaMax_Output" Then EnableorDisable Else SetUpLogic End If End Sub Sub EnableorDisable() With ActiveSheet If .OptionButton1.Caption = "A" Or .OptionButton1.Caption = "D" Then .OptionButton1.Enabled = True Else .OptionButton1.Enabled = False End If If .OptionButton2.Caption = "A" Or .OptionButton2.Caption = "D" Then .OptionButton2.Enabled = True Else .OptionButton2.Enabled = False End If If .OptionButton3.Caption = "A" Or .OptionButton3.Caption = "D" Then .OptionButton3.Enabled = True Else .OptionButton3.Enabled = False End If If .OptionButton4.Caption = "A" Or .OptionButton4.Caption = "D" Then .OptionButton4.Enabled = True Else .OptionButton4.Enabled = False End If If .OptionButton5.Caption = "A" Or .OptionButton5.Caption = "D" Then .OptionButton5.Enabled = True Else .OptionButton5.Enabled = False End If If .OptionButton6.Caption = "A" Or .OptionButton6.Caption = "D" Then .OptionButton6.Enabled = True Else .OptionButton6.Enabled = False End If If .OptionButton7.Caption = "A" Or .OptionButton7.Caption = "D" Then .OptionButton7.Enabled = True Else .OptionButton7.Enabled = False End If If .OptionButton8.Caption = "A" Or .OptionButton8.Caption = "D" Then .OptionButton8.Enabled = True Else .OptionButton8.Enabled = False End If If .OptionButton9.Caption = "A" Or .OptionButton9.Caption = "D" Then .OptionButton9.Enabled = True Else .OptionButton9.Enabled = False End If If .OptionButton10.Caption = "A" Or .OptionButton10.Caption = "D" Then .OptionButton10.Enabled = True Else .OptionButton10.Enabled = False End If If .OptionButton11.Caption = "A" Or .OptionButton11.Caption = "D" Then .OptionButton11.Enabled = True Else .OptionButton11.Enabled = False End If If .OptionButton12.Caption = "A" Or .OptionButton12.Caption = "D" Then .OptionButton12.Enabled = True Else .OptionButton12.Enabled = False End If If .OptionButton13.Caption = "A" Or .OptionButton13.Caption = "D" Then .OptionButton13.Enabled = True Else .OptionButton13.Enabled = False End If If .OptionButton14.Caption = "A" Or .OptionButton14.Caption = "D" Then .OptionButton14.Enabled = True Else .OptionButton14.Enabled = False End If End With End Sub . |
Active X not updating properties on Excel Sheet
Repaint doesn't work on a worksheet - a worksheet is in the excel object
library. Repaint is in the MSforms object library. To the OP, is screenupdating enabled? In any event try DoEvents Application.ScreenUpdating = False Application.ScreenUpdating = True after you make the change. -- Regards, Tom Ogilvy steve wrote in message ... The Repaint also works on the page object. (I don't know the syntax, but try out a few things) Maybe Activesheet.Repaint Sheets("Sheet1").Repaint Check out VB Help on Repaint. -- sb "Please Help Again" wrote in message ... Steve, thanks for the tip, but the repaint function is not working. The option buttons are embedded into an Excel sheet. They are not linked to any cells, but they are not on a user form. I am thinking the only way to get around this is to create a form for the user button to be on, but I do not want to do that, as the excel sheet is the application, and the option buttons are userful the entire time the sheet is open. I feel as though there is something that I am missing. It is like a refresh or repaint thing, but nothing is working! Thanks for any help you may have on this -----Original Message----- Look into Userform1.Repaint after all your changes. -- sb "Please Help" wrote in message ... I have some option buttons that I change the caption, disable or enable based on a certain sheets value in a workbook. On the activate event I reset the properties, enable or disable. The problem is - the values are not changing on the screen. I attempted to refresh the workbook, and they still do not change the values. When I put the active x controls into design mode, the values are how I set them in code, i.e., correct. But - the screen doesn't display it correctly. Can anyone tell me what I am doing wrong?? I have inserted my code: Private Sub Worksheet_Activate() CleanUpRadioButtons SetDefaultRadio ThisWorkbook.RefreshAll Application.ScreenUpdating = True End Sub Sub CleanUpRadioButtons() With ActiveSheet .OptionButton1.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d9").Value), 1, 1) .OptionButton2.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d10").Value), 1, 1) .OptionButton3.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d11").Value), 1, 1) .OptionButton4.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d12").Value), 1, 1) .OptionButton5.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d14").Value), 1, 1) .OptionButton6.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d15").Value), 1, 1) .OptionButton7.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d16").Value), 1, 1) .OptionButton8.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d17").Value), 1, 1) .OptionButton9.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d18").Value), 1, 1) .OptionButton10.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d19").Value), 1, 1) .OptionButton11.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d20").Value), 1, 1) .OptionButton12.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d21").Value), 1, 1) .OptionButton13.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d23").Value), 1, 1) .OptionButton14.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d24").Value), 1, 1) End With If ActiveSheet.Name = "VistaMax_Input" Or ActiveSheet.Name = "VistaMax_Output" Then EnableorDisable Else SetUpLogic End If End Sub Sub EnableorDisable() With ActiveSheet If .OptionButton1.Caption = "A" Or .OptionButton1.Caption = "D" Then .OptionButton1.Enabled = True Else .OptionButton1.Enabled = False End If If .OptionButton2.Caption = "A" Or .OptionButton2.Caption = "D" Then .OptionButton2.Enabled = True Else .OptionButton2.Enabled = False End If If .OptionButton3.Caption = "A" Or .OptionButton3.Caption = "D" Then .OptionButton3.Enabled = True Else .OptionButton3.Enabled = False End If If .OptionButton4.Caption = "A" Or .OptionButton4.Caption = "D" Then .OptionButton4.Enabled = True Else .OptionButton4.Enabled = False End If If .OptionButton5.Caption = "A" Or .OptionButton5.Caption = "D" Then .OptionButton5.Enabled = True Else .OptionButton5.Enabled = False End If If .OptionButton6.Caption = "A" Or .OptionButton6.Caption = "D" Then .OptionButton6.Enabled = True Else .OptionButton6.Enabled = False End If If .OptionButton7.Caption = "A" Or .OptionButton7.Caption = "D" Then .OptionButton7.Enabled = True Else .OptionButton7.Enabled = False End If If .OptionButton8.Caption = "A" Or .OptionButton8.Caption = "D" Then .OptionButton8.Enabled = True Else .OptionButton8.Enabled = False End If If .OptionButton9.Caption = "A" Or .OptionButton9.Caption = "D" Then .OptionButton9.Enabled = True Else .OptionButton9.Enabled = False End If If .OptionButton10.Caption = "A" Or .OptionButton10.Caption = "D" Then .OptionButton10.Enabled = True Else .OptionButton10.Enabled = False End If If .OptionButton11.Caption = "A" Or .OptionButton11.Caption = "D" Then .OptionButton11.Enabled = True Else .OptionButton11.Enabled = False End If If .OptionButton12.Caption = "A" Or .OptionButton12.Caption = "D" Then .OptionButton12.Enabled = True Else .OptionButton12.Enabled = False End If If .OptionButton13.Caption = "A" Or .OptionButton13.Caption = "D" Then .OptionButton13.Enabled = True Else .OptionButton13.Enabled = False End If If .OptionButton14.Caption = "A" Or .OptionButton14.Caption = "D" Then .OptionButton14.Enabled = True Else .OptionButton14.Enabled = False End If End With End Sub . |
Active X not updating properties on Excel Sheet
Tom,
I sit corrected. I didn't read the Help file correctly... My ouch... -- sb "Tom Ogilvy" wrote in message ... Repaint doesn't work on a worksheet - a worksheet is in the excel object library. Repaint is in the MSforms object library. To the OP, is screenupdating enabled? In any event try DoEvents Application.ScreenUpdating = False Application.ScreenUpdating = True after you make the change. -- Regards, Tom Ogilvy steve wrote in message ... The Repaint also works on the page object. (I don't know the syntax, but try out a few things) Maybe Activesheet.Repaint Sheets("Sheet1").Repaint Check out VB Help on Repaint. -- sb "Please Help Again" wrote in message ... Steve, thanks for the tip, but the repaint function is not working. The option buttons are embedded into an Excel sheet. They are not linked to any cells, but they are not on a user form. I am thinking the only way to get around this is to create a form for the user button to be on, but I do not want to do that, as the excel sheet is the application, and the option buttons are userful the entire time the sheet is open. I feel as though there is something that I am missing. It is like a refresh or repaint thing, but nothing is working! Thanks for any help you may have on this -----Original Message----- Look into Userform1.Repaint after all your changes. -- sb "Please Help" wrote in message ... I have some option buttons that I change the caption, disable or enable based on a certain sheets value in a workbook. On the activate event I reset the properties, enable or disable. The problem is - the values are not changing on the screen. I attempted to refresh the workbook, and they still do not change the values. When I put the active x controls into design mode, the values are how I set them in code, i.e., correct. But - the screen doesn't display it correctly. Can anyone tell me what I am doing wrong?? I have inserted my code: Private Sub Worksheet_Activate() CleanUpRadioButtons SetDefaultRadio ThisWorkbook.RefreshAll Application.ScreenUpdating = True End Sub Sub CleanUpRadioButtons() With ActiveSheet .OptionButton1.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d9").Value), 1, 1) .OptionButton2.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d10").Value), 1, 1) .OptionButton3.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d11").Value), 1, 1) .OptionButton4.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d12").Value), 1, 1) .OptionButton5.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d14").Value), 1, 1) .OptionButton6.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d15").Value), 1, 1) .OptionButton7.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d16").Value), 1, 1) .OptionButton8.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d17").Value), 1, 1) .OptionButton9.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d18").Value), 1, 1) .OptionButton10.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d19").Value), 1, 1) .OptionButton11.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d20").Value), 1, 1) .OptionButton12.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d21").Value), 1, 1) .OptionButton13.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d23").Value), 1, 1) .OptionButton14.Caption = Mid(UCase (Application.Worksheets("Inventory_ws").Range ("d24").Value), 1, 1) End With If ActiveSheet.Name = "VistaMax_Input" Or ActiveSheet.Name = "VistaMax_Output" Then EnableorDisable Else SetUpLogic End If End Sub Sub EnableorDisable() With ActiveSheet If .OptionButton1.Caption = "A" Or .OptionButton1.Caption = "D" Then .OptionButton1.Enabled = True Else .OptionButton1.Enabled = False End If If .OptionButton2.Caption = "A" Or .OptionButton2.Caption = "D" Then .OptionButton2.Enabled = True Else .OptionButton2.Enabled = False End If If .OptionButton3.Caption = "A" Or .OptionButton3.Caption = "D" Then .OptionButton3.Enabled = True Else .OptionButton3.Enabled = False End If If .OptionButton4.Caption = "A" Or .OptionButton4.Caption = "D" Then .OptionButton4.Enabled = True Else .OptionButton4.Enabled = False End If If .OptionButton5.Caption = "A" Or .OptionButton5.Caption = "D" Then .OptionButton5.Enabled = True Else .OptionButton5.Enabled = False End If If .OptionButton6.Caption = "A" Or .OptionButton6.Caption = "D" Then .OptionButton6.Enabled = True Else .OptionButton6.Enabled = False End If If .OptionButton7.Caption = "A" Or .OptionButton7.Caption = "D" Then .OptionButton7.Enabled = True Else .OptionButton7.Enabled = False End If If .OptionButton8.Caption = "A" Or .OptionButton8.Caption = "D" Then .OptionButton8.Enabled = True Else .OptionButton8.Enabled = False End If If .OptionButton9.Caption = "A" Or .OptionButton9.Caption = "D" Then .OptionButton9.Enabled = True Else .OptionButton9.Enabled = False End If If .OptionButton10.Caption = "A" Or .OptionButton10.Caption = "D" Then .OptionButton10.Enabled = True Else .OptionButton10.Enabled = False End If If .OptionButton11.Caption = "A" Or .OptionButton11.Caption = "D" Then .OptionButton11.Enabled = True Else .OptionButton11.Enabled = False End If If .OptionButton12.Caption = "A" Or .OptionButton12.Caption = "D" Then .OptionButton12.Enabled = True Else .OptionButton12.Enabled = False End If If .OptionButton13.Caption = "A" Or .OptionButton13.Caption = "D" Then .OptionButton13.Enabled = True Else .OptionButton13.Enabled = False End If If .OptionButton14.Caption = "A" Or .OptionButton14.Caption = "D" Then .OptionButton14.Enabled = True Else .OptionButton14.Enabled = False End If End With End Sub . |
| All times are GMT +1. The time now is 10:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com