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

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

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

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