By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,140 Members | 1,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,140 IT Pros & Developers. It's quick & easy.

Return to Previous Value When Using Combo Box in Excel

P: 31
I have a combo box with a pick list in excel.
The combo box fires on double click of a cell with validation
If the user then presses the escape key I wnat the linked cell to return to the previous value.

So far I have got the double click to copy the original value into cell "A1"

Then I have the following code on keystroke for the combo
Expand|Select|Wrap|Line Numbers
  1. Private Sub PickCombo_KeyDown(ByVal _
  2.         KeyCode As MSForms.ReturnInteger, _
  3.         ByVal Shift As Integer)
  4.  
  5.     Application.ScreenUpdating = False
  6.  
  7.     Select Case KeyCode
  8.  
  9.     Case 27 'escape
  10.  
  11.             Range("A1").Copy
  12.             ActiveCell.Offset(0, 1).Select
  13.             Application.ScreenUpdating = False
  14.             Selection.Offset(0, -1).Select
  15.             Application.ScreenUpdating = False
  16.             Selection.PasteSpecial Paste:=xlPasteValues
  17.             Application.CutCopyMode = False
  18.  
  19.         Case 9 'Tab
  20.         Application.ScreenUpdating = False
  21.             ActiveCell.Offset(0, 1).Select
  22.             Application.ScreenUpdating = False
  23.             Selection.Offset(0, -1).Select
  24.             Application.ScreenUpdating = False
  25.             Selection.Copy
  26.             Selection.PasteSpecial Paste:=xlPasteValues
  27.             Application.CutCopyMode = False
  28.         Case 13 'Enter
  29.             ActiveCell.Offset(0, 1).Select
  30.             Application.ScreenUpdating = False
  31.             Selection.Offset(0, -1).Select
  32.             Application.ScreenUpdating = False
  33.             Selection.Copy
  34.             Selection.PasteSpecial Paste:=xlPasteValues
  35.             Application.CutCopyMode = False
  36.  
  37.  
  38.         Case Else
  39.     End Select
  40.     Application.ScreenUpdating = True
  41. End Sub
  42.  
This means if enter or tab are used the value is returned then copied and pasted over itself to fire an ON Change event.
I can't get the original back in with escape though, trying to copy it back from A1.

How do I get Excel to paste in the original target cell (the one that opened the combo), considering that cell could be anywhere in the worksheet because the combo box will fire on any cell with validation = 3.

The active cell changes to A1. This is why it won't paste in the correct place. How do I get it back to the original cell that called the combo from A1 after copying it?
Apr 4 '12 #1
Share this Question
Share on Google+
1 Reply


P: 31
Sorry guys, I worked this out. Posted to soon due to frustration.

I needed to monitor the target cell on the double click before this and use the monitored cell to get back in the Keydown.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  2. monitored2 = ActiveCell.Address
  3. ..... 'rest of code to call combo
then
Expand|Select|Wrap|Line Numbers
  1. Private Sub PickCombo_KeyDown(ByVal _
  2.         KeyCode As MSForms.ReturnInteger, _
  3.         ByVal Shift As Integer)
  4.     Select Case KeyCode
  5.         Case 9 'Tab
  6.             Range("A1").Copy
  7.             Range(monitored2).Select
  8.             Selection.PasteSpecial Paste:=xlPasteValues
  9.             Application.CutCopyMode = False
  10. .... code for tab and enter
Apr 4 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.