dimanche 19 avril 2015

Issue with Excel VBA range as global variable

I'm having a UserForm, which i use for fast data entry. When the UserForm get initialized some shortcuts are assigned for different macros. One of these macros deletes whole lines from my data sheet. Another macro should be used to restore such a deletion. For this i use a range as global variable for backup of the deletion. However, the range is not in scope..


Please see the relevant code snippets and the error:


Form Code:



Public Sub UserForm_Initialize()

'set shortcuts
'...

Application.OnKey "{BACKSPACE}", "delete_row"
Application.OnKey "^z", "redo_row"
'...

'<curRow> is global variable (in Module1) which is needed for PicBro Userform..
curRow = ActiveCell.Row

'...
End Sub





Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

'...

'release shortcuts
Application.OnKey "{BACKSPACE}"
Application.OnKey "^z"
'...

End Sub


Module 1 code:



'global variables in module1
'****************************
Public curRow As Long
Public rngBU As Range
'****************************
Public Sub delete_row()

'...
Set rngBU = Data.Rows(curRow)
MsgBox "Copied row " & rngBU.Row & " for backup.."

Data.Rows(curRow).delete Shift:=xlUp
Data.Cells(curRow, 1).Activate

'...
End Sub





Public Sub redo_row()
MsgBox "Restoring deleted row " & rngBU.Row & " from backup.." 'ERROR runtime 424
rngBU.Copy
Data.Rows(curRow).Insert Shift:=xlDown
End Sub

Aucun commentaire:

Enregistrer un commentaire