Can't Edit Cell Values After Clicking Excel UserForm

Question

When I click a button on my UserForm it goes to the relevant sheet (via .activate and then End Sub, but I have also tried .select) but I cannot edit the cell. However, when I click into the sheet normally via the bottom pane I can edit it again.

I have not found an excel log or process manager so I cannot see what macros could be running or data that is loading* and could be affecting this - does anyone have any idea of the possible reasons I can't edit cell values after using the UserForm?

Here's my code for the button in question (I added the Unload Me part in the hope it would stop any additional UserForm subs leftover:

    Private Sub CommandButton1_Click()
    Sheets("2H Campaigns View").Select
    Unload Me
    End Sub

*Our sheets use quite a bit of external data but if this were the case I assume clicking the bottom pane to edit wouldn't work either...

I have also tried shutting out of the UserForm (and nay possible macros its running) immediately after click with Unload Me but to no avail.

EDIT: I put a print to cell function (on a separate debug sheet) in at the end of each sub to check if there are any others running after the button is clicked, but it reads that the button (the above sub) is the last sub to run. Thus its safe to assume that the problem is unrelated to the below subs; isolated to either the button sub or something which the running of the button sub does to the settings for the workbook....

Other Subs used in this sheet (all under UserForm - cbSector_Change and UserForm_initalize draw sheet names in for two menus that categorize the pages; one is a sub menu of the other):

Private Sub cbSector_Change()
If cbSector.Value = "DIST" Then
With cbCampaign
    .RowSource = Worksheets("Master Data").Range("G13").Value
    .ListRows = Worksheets("Master Data").Range("H14").Value
    .Value = Worksheets("Master Data").Range("b16").Value
End With
ElseIf cbSector.Value = "INDU" Then
With cbCampaign
    .RowSource = Worksheets("Master Data").Range("gl7").Value
    .ListRows = Worksheets("Master Data").Range("h17").Value
    .Value = Worksheets("Master Data").Range("b16").Value
End With
ElseIf cbSector.Value = "CS" Then
With cbCampaign
    .RowSource = Worksheets("Master Data").Range("gl8").Value
    .ListRows = Worksheets("Master Data").Range("h18").Value
    .Value = Worksheets("Master Data").Range("b16").Value
End With
End If
End Sub

Private Sub EButton_Click()
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub

Private Sub SEButton_Click()
ThisWorkbook.Save
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub

Private Sub UserForm_Initialize()

With cbSector
    .RowSource = Worksheets("Master Data").Range("b13").Value
    .ListRows = Worksheets("Master Data").Range("b14").Value
    .Value = Worksheets("Master Data").Range("b12").Value
End With
End Sub
Private Sub cbSelect_Click()

If cbSector.Value = "(none)" Then
    errormsg = "Please Select Sector"
ElseIf cbCampaign.Value = "(none)" Then
    errormsg = "Please Select Campaign"
Else: errormsg = "nothing"
End If

If errormsg = "nothing" Then
      Sheets(cbSector.Value & "_" & cbCampaign.Value).Select
      Unload Me
    Else: MsgBox (errormsg)
End If
End Sub

To launch the UserForm this code is attached to a button on all but one of the sheets in the workbook:

Public SheetSelected As Worksheet
Public errormsg As String
Sub CallUserForm()
nav.Show
End Sub

Yes, but when I hit return after editing it reverts back to the original value. Likewise you can't click the tick in the top/formula bar after entering a new value. — Diz
Why is my question getting downvoted? I am new here so some explanation would be nice. I searched thoroughly before asking to no avail. — Diz
@DesConlon I didn't downvote you but find it understandable why someone would. You are essentially asking us to debug code that we can't see. We could try to guess -- but why bother? Perhaps if you show us the code for the relevant button it would help. — John Coleman
Edited. My apologies John but its literally three lines; if I thought it relevant I would have included it initially. — Diz

John Coleman John Coleman · Accepted Answer · 2015-08-17T15:46:16

This is a partial answer to the last question you asked in the comments ("would there be any way to hack a manual sheet tab click in vba"?) I don't know how to do that directly, but here is an ugly hack which simulates using the Ctrl+PgUp and Ctrl+PgDn keyboard shortcuts to tab from one worksheet to another:

Sub PageToSheet(SheetName As String)
    Dim here As Long, there As Long, i As Long

    here = ActiveSheet.Index
    there = Sheets(SheetName).Index
    If here = there Then
        Exit Sub
    ElseIf here < there Then
        For i = 1 To there - here
            Application.SendKeys "^{PGDN}"
        Next i
    Else
        For i = 1 To here - there
            Application.SendKeys "^{PGUP}"
        Next i
    End If
End Sub

This won't work when the VBA editor is the active window. But if you launch the following test sub while the main Excel window is active it seems to work:

Sub test()
    Dim s As String
    s = InputBox("Enter name of sheet to go to")
    PageToSheet s
End Sub

It would be better to try to track down the source of the bug that you are seeing and even experiment with transferring all data and code to a new workbook to make sure that you don't have an inexplicable corruption in the file itself (which is sometimes what is behind truly weird behavior). Still -- if you want to simulate manual page tabs you can via SendKeys.

Can't Edit Cell Values After Clicking Excel UserForm

1 Answers