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.
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.
