Inserting a row below a combobox determined cell

[pacsol_mic]

I have a list array named electrical that has all the prices for the items i
use. I have a prompt box to insert a new item . I want it to insert right
below the category name that is determined by the combobox then copy formulas
from a generic row so the calculations are inputed. I can't seem to figure
out how to reselect the row that i just inserted it .
Private Sub CommandButton1_Click()
Dim cell As Range
Dim SELECTEDCELL As Range

For Each cell In Sheet7.Range("electrical")
If PromptInsertItem.Category.Value = cell.Value Then
cell.Offset(1, 0).Insert SHIFT:=xlDown

End If
Next


Range("A18:x18").Select
Selection.Copy
ActiveSheet.Paste



PromptInsertItem.Hide

End Sub

 

[OssieMac]

I am not sure that I understand correctly but am I correct in that you want
to re-select the last selection prior clicking the command button?

If the above is correct then you could use a worksheet selection change
event and always save the selection. However, the varible needs to be
declared in the Declarations area at the top of the module before any subs.

Dim SELECTEDCELL As Range

Private Sub CommandButton1_Click()
Dim cell As Range

For Each cell In Sheet7.Range("electrical")
If PromptInsertItem.Category.Value = cell.Value Then
cell.Offset(1, 0).Insert SHIFT:=xlDown

End If
Next

Range("A18:x18").Select
Selection.Copy
ActiveSheet.Paste

PromptInsertItem.Hide

SELECTEDCELL.Select

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set SELECTEDCELL = Target
End Sub