Yes .realised after I sent it ...:-( seems the button press action is left active when sheet changes .could selecting a cell on the sheet before switching active sheet help
Regards Ralph
----- Reply message -----
From: "noskosteve" <noskosteve@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Subject: [ExcelVBA] Re: Sheet CommandButton becomes "crosshatched" and comatose.
Date: Sat, Jun 29, 2013 1:55 am
I guess you misssed it. That's what is there. See end of this post for the code.
Steve
--- In ExcelVBA@yahoogroups.com, Ralph <ralph.gregory@...> wrote:
>
> What happens if you put in exit sub after activate sheet 3
>
> Regards, Ralph
>
> noskosteve <noskosteve@...> wrote:
> >Maybe I should just take up knitting.
> >
> >Famous last words: Everything worked fine until...
> >
> >I have a Sheet1.CommandButton that normally brings up a UserForm for some preferences to be entered/changed by the user and works fine.
> >
> >I added a function which does not need these prefs. It's just a checkBox on Sheet3. When that function is activated, I bring up a message box indicating that the function must be disabled (unchecked) to see the Prefs' UserForm. Then, I exit the CommandButton_Click Sub without Showing the UserForm. Life is still good.
> >
> >Being the nice guy that I am, I figured it would be helpful to also automaticlly activate sheet3 which has the checkBox, so the user can easily un-check it and go back and see the UserForm to enter these prefs.
> >
> >Simply adding the Sheet3.Activate, causes the Sheet1.CommandButton to become crosshatched (when I reselect sheet1) in otder to to click it and enter the prefs.
> >It is full of diagonal lines and unresponsive.
> >
> >When clicking the 'crossed-out' CommandButton, the crosshatch goes away, but it is now non-responsive. The Click Event does not fire the Click Sub.
> >VBA doesn't appear to be paused or haulted in any way - other things work normally.
> >
> >Doing just about anything else that causes some other VBA code to execute then re-activates this catatonic CommandButton.
> >
> >Clicking the Reset button in the VBA window does not awaken the comatose CommandButton, nor change the title bar indicating VBA has changed modes (like going out of debug mode)
> >
FWIW here's the Click sub with the problem (near the end):
----------------------------------------------
Private Sub PrefsButton_Click() ' Put filter prefs into Form
Debug.Print "S_1 2 Filter Prefs Button Clicked. ";
'This sub is in Sheet1.
'Look at the ELSE for where the 'crosshatch' problem occurs...
If Sheet3.Select_2000 Then ' The new function's CheckBox.
'All the stuff from here to the Else workes fine:
'Get Pref values into the UserForm
'SSB
userform1.SB_Upper_Narrow = sheet1.Range ("SSB_Narrow_Upper_Pref").Value
userform1.SB_Upper_Mid = sheet1.Range("SSB_Mid_Upper_Pref").Value
userform1.SB_Upper_Wide = sheet1.Range("SSB_Wide_Upper_Pref").Value
'...
'There is more of the same kind of setting here, not shown.
'...
userform1.Show ' When execution get here, this works.
Else ' Abort the filter settings form
MsgBox "Filter settings from this sheet are only for the TS-2000. To Change them, change the radio type to TS-2000 on the Memories sheet.", vbExclamation, " CAUTION CAUTION CAUTION"
'Now, Trouble. Activate the Memories sheet for the user to more easily make the change.
Sheet3.Activate ' Adding this ONE line causes this CommandButton to become "crosshatched" when re-selecting Sheet1 !
'I've tried other things here like:
'End, or
'Reset, or
'PrefsButton.Activate to no avail.
Exit Sub
End If
End Sub
>-------------------------------------------------
Sigh, Steve
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (1) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar