On Cell Selection issue

GAJITCS

Board Regular
Joined
Apr 21, 2015
Messages
50
Hello,

I have a quoting system in development. Raising a quote is not a problem. One of the features is that a combobox is displayed when certain cells are clicked upon and at the same time, a predictive search allows for short-cutting to the desired option. For example, when we click the "Senders Name" field, a list of company names is displayed and as we type, so the list is filtered.

Once a company is picked from the list, the address details for that company a populated within the quote and can be amended for that quote only (no option to overwrite the later lookup table).

That all works.

The issue is that when I reload the quote details from the saved quote whereby it may then be amended, I need for a few things to happen.

Firstly, that the address details as originally saved, are shown. This works.
Second, that an amendment to anything other than the company name does not change other details on the quote. This works.
Third, If the Company Name field is selected, a warning is displayed noting that making a change to the company, or even selecting it again from the displayed list will overwrite any amendment to the address fields that may have been manually overwritten when the quote was first generated.

It's this third one that is causing the problem. I'm certain it's because of the code I have in place that allows the filtered combobox to work. When I click the Company Name cell, it's the combo box that is displayed and ther is seemingly no recognition that the cell behind the box has been selected.

This is the VBA for the Combobox.

_______
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
________

Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
_______


I have no proficiency with VBA whatsoever.

Any help will be appreciated.

Many thanks.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,693
Members
416,127
Latest member
MALEPINZON

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top