SendKeys {F2}

[drewg]

I am trying to change the attributes of a worksheet so that it can be edited.
When I'm in the VBA editor and stepping through the code, when the code hits the SendKeys line, it wakes up the Object Browser, which is also listenig for F2 i guess.
Can I get a clue as to what am i doing wrong? is there something like xlEditMode i should be using instead?
Thanks very much

 

[healey21]

F2 is open the object browser, so you would need to test the code on the worksheet side rather than VBA is my suggestion. Or possibly add a statement to the debug window and comment the line out for the stepping through the code.

 

[drewg]

Thanks very much, like driving in the rear view mirror...

 

[Rick Rothstein]

I am not sure exactly what you are trying to do, but you should keep in mind that VBA code cannot run when a cell on the worksheet is placed into Edit Mode (like when you press the F2 key).

 

[drewg]

Thanks very much for your reply. This is what I am trying to accomplish

Interrupt and resume execution or something else?

This is where I am at with it, and I would be most appreciative of any suggestions you may have.
Thanks for your time and attention

Option Explicit
 Public sSheetToEdit As String
 Public lMyWorksheetNumber As Long
 Public Const EDIT_MSG_STARTS_AT = "C1"
 Public Const CMD_RETURN_TO_SUMMARY_LOC = "H1"
 Public Const PW ="Abnormal"

Sub EditSelectedWorksheet(ByVal sSheetToEdit As String)
    
    Application.SendKeys Keys:="{F2}"
    
    'Hide all the other worksheets except the one being edited
    Call MakeSheetsInvisible
    
    'hide the form
    frmScopeHeader.Hide
    
    'lock down the worksheet were editing, sSheetToEdit
    Call LocKDownAll
    
    'unlock the edit area ...all material workbooks have a data entry area
     ThisWorkbook.Worksheets(sSheetToEdit).Range("Data_Entry").Unprotect Password:=PW
    
    'warn the user they are in edit mode
    Call PostEditModeMessage 'maybe locate the message relative to the data_entry area?
    
    'Create a buttom to get back to the Summary sheet after the edit is over
    Call CreateReturnToSummaryButton
    
    'And the edit session is underway
    ThisWorkbook.Worksheets(sSheetToEdit).Activate
    'wait for the user to complete his entry
    'transfer the Breakdown range to Scope
    'Get back to the form on ScopeSheet.


End Sub
Sub MakeSheetsInvisible()
Dim i As Long, _
lMyWorksheetNumber As Long


lMyWorksheetNumber = Mid(sSheetToEdit, 1, 1)
    
    For i = 1 To ThisWorkbook.Worksheets.Count
        
        If Mid(ThisWorkbook.Worksheets(i).Name, 1, 1) <> lMyWorksheetNumber Then
            ThisWorkbook.Worksheets(i).Visible = False
        End If
    
    Next i


End Sub


Sub LocKDownAll()
' SelectAndLocKDownAll Macro
    ActiveCell.Cells.Select
    ActiveSheet.Protect Password:=PW, DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


Sub PostEditModeMessage()
    
    ActiveCell.Offset(0, 3).Range(EDIT_MSG_STARTS_AT).Select
    ActiveCell.FormulaR1C1 = "Please Note You Are in Edit Mode"
    ActiveCell.Select
    
    With Selection.Font
        .FontStyle = "Bold"
        .Size = 14
        .Color = 255
    End With
    
    Selection.MergeCells = True


End Sub


Sub CreateReturnToSummaryButton()
    ActiveCell.Offset(0, 1).Range(CMD_RETURN_TO_SUMMARY_LOC).Select
    ActiveSheet.Buttons.Add(388.5, 0.75, 185.25, 33.75).Select
    Selection.OnAction = "RestoreSourceAfterEdit"                       'name of the macro to run
    Selection.Characters.Text = "Return To Scope Sheet"                 'caption
    Selection.Characters.Name = "CommandButton1"                        'not sure there is any such thing
    
    With Selection.Characters(Start:=1, Length:=21).Font
        .FontStyle = "Regular"
        .Size = 11
        .ColorIndex = 1
    End With


End Sub


Sub CommandButton1_Click()


'    Leave Edit Mode
    SendKeys "+{F2}"


'   Erase the Edit Mode Warning message
    ThisWorkbook.Worksheets(sSheetToEdit).Range(EDIT_MSG_STARTS_AT).Value = ""


'    Make worksheets visible again
    Call ShowAllWorksheets


'    Unlock the cells
    ActiveSheet.Unprotect PW


'    Hide or delete the OK Button and the warning message
    ActiveSheet.Shapes("CommandButton1").Delete


'    Update Summary with the refreshed values from Target


    frmScopeHeader.Show
    Call CopyTheBreakdown(sSheetToEdit)
    ThisWorkbook.Worksheets("Summary").Activate


End Sub


Sub ShowAllWorksheets()
Dim i As Long, _
MyWorksheetNumber As Long, _
wksht As Worksheet
MyWorksheetNumber = Mid(ThisWorkbook.ActiveSheet.Name, 1, 1)
    For i = 1 To ThisWorkbook.Worksheets.Count
            ThisWorkbook.Worksheets(i).Visible = True
    Next i


End Sub

 

[Rick Rothstein]

If I understand what you want to do correctly, you are trying to run some code, control what cells the user should enter data in, switch to Edit Mode in order for the user to be able to enter his/her data and then have your code resume executing. If so, as far as I know, you cannot do it that way. The approach that I think you would need to use is to create a UserForm with, at minimum, TextBoxes on it to display the data and allow the user to edit it plus a CommandButton to process that edited data. The UserForm's Initialize event would load the TextBoxes up and the CommandButton's Click event would write the data back to the cells the text was originally drawn from.

 

[drewg]

Thanks very much for your approach. I understand that to accomplish the continuity of the add/edit process, the target worksheet would have to be shown in text boxes so the edits would happen under control of the UserForm. I will have to check with the users to see if that will fly. Big picture, what i think from your guidance is I will abandon trying to maintain continuation in the same process. Once the user has landed on the target to do their edit, that's the end of the first form. An Ok or Close button on the target can update the range on the Summry sheet and ship the changed data back to the summary sheet and activate it. Then, if they want to continue, they can reactivate the form, which will know if it has rows in its detail area or not, so it knows how to show itself.
Thanks for your thoughts, it was a big help.