Combobox search & populate macro resets another combobox on close/open & with active selection change.

[DFragnDragn]

Well Gang, I'm either stumped or brain dead. Probably more stumped & just half brain dead.

I need help with an issue, please.

The attached macro populates combobox3 - (2 column) with a list of currency names & their acronyms in column 2 for list selections.
The acronyms are also furnished to several cells via CONCATENATE formulas. --No problems there!

I should add that this combobox3 selection also changes the acronym selections in combobox1 via CONCATENATE & an off-sheet cell value furnished by the combobox3 selection.

The issue is with combobox1. Firstly, should the user make another selection it resets combobox1 & clears the related user data input cell, in order to replace the CONCATENATE 'd acronym. --> That I can live with just fine!- I have conditional formatting to provide a heads-up to the user regarding lost input.

The problem is with saving the workbook! Upon reopening the workbook XL zips through the code activating & resetting Combobox1 wiping out the saved data! That's not my idea of saving data! HELP...

I hope I've been succinct enough.

Any ideas anyone?

BTW this code block also ties together a related shape image (top portion) to selections & hides/shows the images as they're replaced by selection (lower portion)

Private Sub ComboBox3_Change()

[COLOR=#0000ff]'Locate picture for display*********************[/COLOR]

If Sheets("Sheet1").Range("C21").Value > 0 Then
    Dim oSht As Worksheet
    Dim lastRow As Long, i As Long
    Dim strSearch As String
    Dim t As Long
    Dim bCell As Range
  On Error Resume Next
    Set oSht = Sheets("Sheet6")
            lastRow = oSht.Range("E" & Rows.Count).End(xlUp).Row
            strSearch = Sheets("Sheet1").Range("C21").Value
    Set bCell = oSht.Range("E1:E" & lastRow).Find(What:=strSearch, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        ActiveSheet.Shapes("Picture " & bCell).Visible = False
If Not bCell Is Nothing Then
On Error GoTo 0
End If
End If

[COLOR=#b22222]'END****************************************************[/COLOR]
[COLOR=#008080]
[/COLOR][COLOR=#0000ff]'Problem Block**********************************************[/COLOR]

On Error Resume Next
If Sheets("Sheet1").Range("C20").Value > 0 Then
    Dim aCell As Range
        Set oSht = Sheets("Sheet6")
            lastRow = oSht.Range("D" & Rows.Count).End(xlUp).Row
            strSearch = Sheets("Sheet1").Range("C20").Value
        Set aCell = oSht.Range("D1:D" & lastRow).Find(What:=strSearch, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
           Sheets("Sheet1").Range("C21").Value = aCell.Offset(0, 1).Value
End If
End If  

[COLOR=#b22222]'END***************************************************[/COLOR]

[COLOR=#0000cd]'Unrelated Image Control************************************[/COLOR]

Dim cCell As String
  If Sheets("Sheet1").Range("C21").Value > 0 Then
        cCell = Sheets("Sheet1").Range("C21").Value
    ActiveSheet.Shapes("Picture " & cCell).Visible = True
    Else
    End If
If Sheets("Sheet1").Range("C21").Value <> "" Then
    ActiveSheet.Shapes("Rectangle Select Currency").Visible = False
    Else
End If
If Sheets("Sheet1").Range("C21").Value <> "Select Currency" Then
    ActiveSheet.Shapes("Rectangle Select Currency").Visible = False
    Else
End If
If Sheets("Sheet1").Range("C21").Value = "" Then
    ActiveSheet.Shapes("Rectangle Select Currency").Visible = True
    Else
End If
    If Sheets("Sheet1").Range("C21").Value = "Select Currency" Then
    ActiveSheet.Shapes("Rectangle Select Currency").Visible = True
    Else
End If
Application.Goto Range("A1"), True
On Error GoTo 0
End Sub

 

[DFragnDragn]

Yep stumped because I was Brain-dead from working all night.

ISSUE is SOLVED
My problem was with Combobox1 macros. I just needed some additional macros involved.