Retrieve value from Combo Box and Radio Button
Hi,
I have two questions about Excel VBA and hope any experts can give me a hint (1) I wonder how to retrieve automatically the previously saved values from the combo box and a group of radio buttons whenever I open the workbook? (2) Everytime I need to insert an object, I copy the object to one worksheet (named as SLD) from another worksheet (named as Lib) which acts like a library. However, because of using the function "SELECT", I encounter "flickering" on the main sheet (named as Sheet1) each time an object is inserted. Is there any way that I could eliminate this flickering? Your advices are greatly appreciated. Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer) Sheets("LIB").Select Select Case myType Case 1 ActiveSheet.Shapes("Liboff1x3wdgtrfr").Select Selection.Copy If myValue = 1 Then Sheets("SLD").Select ActiveSheet.Paste Selection.Name = "offtrfr1x3wdgoff1" Selection.Left = 380 Selection.Top = 642 End If End Select Sheet1.Activate |
Retrieve value from Combo Box and Radio Button
1.) Put this code in the Workbook module. Excel doesn't remember what your
last value was for your controls, so what you will need to do is store the values in cells. In this example, I stored the value of an option button and combobox in the Lib worksheet when the workbook is closed. You can choose whichever event is best in your application. And you may need to change the controls names as well. Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheets("Lib").Range("A1").Value = OptionButton1.Value Sheets("Lib").Range("A2").Value = ComboBox1.Value End Sub Private Sub Workbook_Open() MsgBox "Option Button 1 = " & Range("A1").Value MsgBox "Combo Box 1= " & Range("A2").Value End Sub 2.) To stop the "flickering" just use this: Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer) Application.ScreenUpdating = False ' your code here Application.ScreenUpdating = True End Sub Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "newbie" wrote: Hi, I have two questions about Excel VBA and hope any experts can give me a hint (1) I wonder how to retrieve automatically the previously saved values from the combo box and a group of radio buttons whenever I open the workbook? (2) Everytime I need to insert an object, I copy the object to one worksheet (named as SLD) from another worksheet (named as Lib) which acts like a library. However, because of using the function "SELECT", I encounter "flickering" on the main sheet (named as Sheet1) each time an object is inserted. Is there any way that I could eliminate this flickering? Your advices are greatly appreciated. Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer) Sheets("LIB").Select Select Case myType Case 1 ActiveSheet.Shapes("Liboff1x3wdgtrfr").Select Selection.Copy If myValue = 1 Then Sheets("SLD").Select ActiveSheet.Paste Selection.Name = "offtrfr1x3wdgoff1" Selection.Left = 380 Selection.Top = 642 End If End Select Sheet1.Activate |
Retrieve value from Combo Box and Radio Button
Thanks Very much Ryan. I have finally solved the problems using your code!
Best wishes, Loy "Ryan H" wrote: 1.) Put this code in the Workbook module. Excel doesn't remember what your last value was for your controls, so what you will need to do is store the values in cells. In this example, I stored the value of an option button and combobox in the Lib worksheet when the workbook is closed. You can choose whichever event is best in your application. And you may need to change the controls names as well. Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheets("Lib").Range("A1").Value = OptionButton1.Value Sheets("Lib").Range("A2").Value = ComboBox1.Value End Sub Private Sub Workbook_Open() MsgBox "Option Button 1 = " & Range("A1").Value MsgBox "Combo Box 1= " & Range("A2").Value End Sub 2.) To stop the "flickering" just use this: Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer) Application.ScreenUpdating = False ' your code here Application.ScreenUpdating = True End Sub Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "newbie" wrote: Hi, I have two questions about Excel VBA and hope any experts can give me a hint (1) I wonder how to retrieve automatically the previously saved values from the combo box and a group of radio buttons whenever I open the workbook? (2) Everytime I need to insert an object, I copy the object to one worksheet (named as SLD) from another worksheet (named as Lib) which acts like a library. However, because of using the function "SELECT", I encounter "flickering" on the main sheet (named as Sheet1) each time an object is inserted. Is there any way that I could eliminate this flickering? Your advices are greatly appreciated. Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer) Sheets("LIB").Select Select Case myType Case 1 ActiveSheet.Shapes("Liboff1x3wdgtrfr").Select Selection.Copy If myValue = 1 Then Sheets("SLD").Select ActiveSheet.Paste Selection.Name = "offtrfr1x3wdgoff1" Selection.Left = 380 Selection.Top = 642 End If End Select Sheet1.Activate |
Retrieve value from Combo Box and Radio Button
If the code helped you click the "YES" button next to "Was this post helpful
to you? I'm trying to earn my "Silver Wings", lol Thanks! -- Cheers, Ryan "newbie" wrote: Thanks Very much Ryan. I have finally solved the problems using your code! Best wishes, Loy "Ryan H" wrote: 1.) Put this code in the Workbook module. Excel doesn't remember what your last value was for your controls, so what you will need to do is store the values in cells. In this example, I stored the value of an option button and combobox in the Lib worksheet when the workbook is closed. You can choose whichever event is best in your application. And you may need to change the controls names as well. Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheets("Lib").Range("A1").Value = OptionButton1.Value Sheets("Lib").Range("A2").Value = ComboBox1.Value End Sub Private Sub Workbook_Open() MsgBox "Option Button 1 = " & Range("A1").Value MsgBox "Combo Box 1= " & Range("A2").Value End Sub 2.) To stop the "flickering" just use this: Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer) Application.ScreenUpdating = False ' your code here Application.ScreenUpdating = True End Sub Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "newbie" wrote: Hi, I have two questions about Excel VBA and hope any experts can give me a hint (1) I wonder how to retrieve automatically the previously saved values from the combo box and a group of radio buttons whenever I open the workbook? (2) Everytime I need to insert an object, I copy the object to one worksheet (named as SLD) from another worksheet (named as Lib) which acts like a library. However, because of using the function "SELECT", I encounter "flickering" on the main sheet (named as Sheet1) each time an object is inserted. Is there any way that I could eliminate this flickering? Your advices are greatly appreciated. Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer) Sheets("LIB").Select Select Case myType Case 1 ActiveSheet.Shapes("Liboff1x3wdgtrfr").Select Selection.Copy If myValue = 1 Then Sheets("SLD").Select ActiveSheet.Paste Selection.Name = "offtrfr1x3wdgoff1" Selection.Left = 380 Selection.Top = 642 End If End Select Sheet1.Activate |
All times are GMT +1. The time now is 10:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com