Results 1 to 2 of 2

Thread: Stop Calculating Worksheet or Stop Updating UserForm TextBox when Hyperlink is Opened

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2009
    Posts
    27

    Stop Calculating Worksheet or Stop Updating UserForm TextBox when Hyperlink is Opened

    I have a UserForm that contains a textbox linking its contents to a cell in my worksheet. The cell in the worksheet contains a formula that counts the number of rows displayed in my worksheet. This way,when I filter on my data, it will tell me how many rows are being displayed with a specific filter applied.

    Below is the code (thanks to Koolsid) that updates my UserForm's textbox whenever the worksheet calculates:

    Code:
    Private Sub Worksheet_Calculate()
        UserForm1.TextBox8.Value = Sheets("OJTDatabase").Range("T12").Value
    End Sub
    The only problem with this is that I have hyperlinks in my worksheet. These hyperlinks are to other spreadsheets. So, when you click on one, you're looking at a new worksheet. Unfortunately, I then get this error:Name:  Run-time-Error-9.jpg
Views: 465
Size:  8.1 KB

    When I click debug, it highlights and sends me right to the code pasted above. Is there a way that I can keep the worksheet from calculating when a hyperlink is opened? Normally, I want the worksheet to calculate whenever something happens, so I don't want to switch calculation to Manual.

    If that's not possible, is there a way that I can keep the textboxes from being updated when a hyperlink is clicked? I mostly want the textboxes to update whenever the data gets filtered. Is it possible to only update the textboxes on a Filter process of some sort?

  2. #2

    Thread Starter
    Junior Member
    Join Date
    Oct 2009
    Posts
    27

    Re: Stop Calculating Worksheet or Stop Updating UserForm TextBox when Hyperlink is Op

    Nevermind, I found a workaround. I just put
    Code:
    Private Sub Worksheet_Calculate()
        UserForm1.TextBox8.Value = Sheets("OJTDatabase").Range("T12").Value
    End Sub
    into a macro that does my filtering for me, and it works well enough...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width