kanadaaa
Active Member
- Joined
- Dec 29, 2019
- Messages
- 348
- Office Version
- 365
- Platform
- Windows
Hi, I'd like help with the following code:
I have the numbers 1-10 as the values for Sheets(1).Range("C1:C10"), and this code works in the way that when A1 is double-clicked, the combobox shows up, so I can choose a value from those numbers.
Then, I would like E1 to be filled automatically with the value in Sheets(2).Range("B2").Value if "2" is selected, and the code works well.
But it doesn't allow me to replace the inserted value in E1 with a new value by manual input, as long as A1 on Sheet1 is filled with "2". (When E1 is clicked out after edit, it still shows the value in Sheets(2).Range("A1").
How could I make it possible to overwrite this value? Thank you.
VBA Code:
Public TargetCell As Range
Private Sub ComboBox1_LostFocus()
TargetCell = ComboBox1.Value
ComboBox1.Visible = False
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Set TargetCell = Target
With ComboBox1
.Height = Target.Height
.Width = Target.Width
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.Font.Size = 14
.List = Range("C1:C10").Value
.ListRows = 10
.Visible = True
.Value = ""
.Activate
.DropDown
End With
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(1, 1) = "2" Then
Cells(1, 5) = Sheets("Sheet2").Cells(2, 2).Value
End If
End Sub
Then, I would like E1 to be filled automatically with the value in Sheets(2).Range("B2").Value if "2" is selected, and the code works well.
But it doesn't allow me to replace the inserted value in E1 with a new value by manual input, as long as A1 on Sheet1 is filled with "2". (When E1 is clicked out after edit, it still shows the value in Sheets(2).Range("A1").
How could I make it possible to overwrite this value? Thank you.
Last edited: