temporarily deactivate a code without using the Apostrophe

still learning · Sep 13, 2014

Hi
Is there a way to temporarily deactivate a code without using the Apostrophe?

I have this code in the worksheet and want to be able to temporarily deactivate it without using the Apostrophe, while I am going back and forth to another sheet. (I used a short macro to make it easier here..some of the sheet macros are long )

HTML:

Private Sub Worksheet_Activate()

  UserForm1.Show
  End Sub

Mike

shg · Sep 13, 2014

How would you want to switch it on and off?

GTO · Sep 13, 2014

Greetings,

When you say "going back and forth to another sheet", do you mean manually, or some code is doing this?

Mark

still learning · Sep 13, 2014

Going back and forth manually to change,copy, or add data I would switch it off manually in the "vba code box". Then, when I'm done editing the data, I would go back to the "box' and switch it on. I Sometimes delete part of the code when I try to delete the apostrophe !!!

Example: I have an event code that I have to disable when I'm editing the sheet. I have a "find last row" code that I have to disable when I have to edit some data (because of mistakes...not mine of course) using another sheet in the workbook.

Mike

gorem · Sep 13, 2014

Are you entering the apostrophe manually? Is that the issue?

If that's not your issue, apologies. If it is, you should be using the "comment block" and "uncomment block" buttons in the toolbar.

still learning · Sep 13, 2014

Yep, I.m entering the apostrophe manually.
I'm using excel 2010 and didn't know about the "comment block" and "uncomment block" buttons

I can't find it in the vba tool bar. I looked in each tab

Mike

shg · Sep 13, 2014

It's on the Edit toolbar. View > Toolbars ...

mikerickson · Sep 13, 2014

One cheap way to do it would be.

Code:

Private Sub Worksheet_Activate()
    If Selection.Address <> "$Z$99" Then
        UserForm1.Show
    End If
End Sub

Then, just select Z99 and go back and forth between sheets and the UF won't appear.

Or you could create a button that will toggle it's caption.

Code:

Sub Button2_Click()
    With ActiveSheet.Shapes(Application.Caller).TextFrame
        .Characters.Text = IIf(.Characters.Text = "Show UF", "No UF", "Show UF")
    End With
End Sub

and use Activate code like

Code:

Private Sub Worksheet_Activate()
    If Me.Shapes("Button 2").TextFrame.Characters.Text = "Show UF" Then
        UserForm1.Show
    End If
End Sub

If you want this disabled as part of your creating the workbook, the apostrophe is just fine.
But if you anticipate wanting this feature as part of the use of the workbook, the user (even if that user is you) should not be expected to open the VB Editor.

still learning · Sep 13, 2014

I found it

HTML:

View>Toolbars>Customize>Edit

I didn’t know anything about that
That’s exactly what I was looking for

Hi mikerickson
I was using Userform1.Show only as an example. I wanted to keep the example short
BUT I'm always interested in seeing code as "That"s how i learn"

Mike

mikerickson · Sep 13, 2014

^^ that's OK, I used BEEP for testing my code. :D

temporarily deactivate a code without using the Apostrophe

still learning

Well-known Member

Excel Facts

shg

MrExcel MVP

GTO

MrExcel MVP

still learning

Well-known Member

gorem

Board Regular

still learning

Well-known Member

shg

MrExcel MVP

mikerickson

MrExcel MVP

still learning

Well-known Member

mikerickson

MrExcel MVP

Similar threads

Forum statistics

Share this page

temporarily deactivate a code without using the Apostrophe

Well-known Member

Excel Facts

MrExcel MVP

MrExcel MVP

Well-known Member

Board Regular

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock