+ Reply to Thread
Results 1 to 8 of 8

"Variable not Defined" ?

  1. #1
    Registered User
    Join Date
    04-21-2017
    Location
    Sunshine Coast, Australia
    MS-Off Ver
    2013
    Posts
    39

    "Variable not Defined" ?

    Hey Guys,
    i'm trying to use a VBA that has a dynamic range as the quantity and value of variables is changing. I've set max and min of the data accodingly for the axis, but i receive an error message of undefined variable for the macro. What am i missing?

    Untitled1.jpgUntitled.jpg
    Last edited by Parmx; 04-24-2017 at 12:59 AM.

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: "Variable not Defined" ?

    Try changing 'Target' to 'ActiveCell' ...

  3. #3
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: "Variable not Defined" ?

    Hi Parmx- 'Target' is NOT a global variable available to just any event procedure. The name "Target" is used PRIVATELY by the Worksheet_Change and Worksheet_SelectionChange events to refer to their pertinent cell(s). If you define a MODULE-LEVEL VARIABLE, you could assign its value in one of those events and use it here.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 04-24-2017 at 01:18 AM.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: "Variable not Defined" ?

    Given the values are in fixed cell locations you should either given those cells names and use the named ranges or just use the static cell addresses.

    Also, as the code is in a button event would you not want to update all values rather than use a select case?
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    04-21-2017
    Location
    Sunshine Coast, Australia
    MS-Off Ver
    2013
    Posts
    39

    Re: "Variable not Defined" ?

    Thanks guys!

    I've managed to find the solution
    However, for some reason when new data is uploaded the range doesn't automatically change, but when i go into VBA and click run it works.
    Does anyone know what is causing this? I tried adding an events enabled but doesn't change anything.

    Private Sub Worksheet_Calculate()
    Dim cht As Chart
    Dim wks As Worksheet

    Set wks = ActiveSheet
    Set cht = wks.ChartObjects("Chart 2").Chart

    If wks.Range("$I$8").Value <> cht.Axes(xlCategory).MaximumScale Then
    cht.Axes(xlCategory).MaximumScale = wks.Range("$I$8").Value
    End If
    If wks.Range("$I$9").Value <> cht.Axes(xlCategory).MinimumScale Then
    cht.Axes(xlCategory).MinimumScale = wks.Range("$I$9").Value
    End If
    If wks.Range("$I$11").Value <> cht.Axes(xlValue).MaximumScale Then
    cht.Axes(xlValue).MaximumScale = wks.Range("$I$11").Value
    End If
    If wks.Range("$I$12").Value <> cht.Axes(xlValue).MinimumScale Then
    cht.Axes(xlValue).MinimumScale = wks.Range("$I$12").Value
    End If
    Application.EnableEvents = True
    End Sub

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: "Variable not Defined" ?

    Please use Code Tags when posting code !! (Forum Rule #3)
    As long as the worksheet isn't recalculated when uploading new data the Calculate-event won't fire.
    Have you tried using Change-event instead ?
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  7. #7
    Registered User
    Join Date
    04-21-2017
    Location
    Sunshine Coast, Australia
    MS-Off Ver
    2013
    Posts
    39

    Re: "Variable not Defined" ?

    It's linked to a cell that is formulated to MAX() and MIN() of the list of new data, so everytime data is uploaded it changes the cell.
    I tried change event but it dosen't fire, and i can't manually click run for it either so not sure

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: "Variable not Defined" ?

    Try running this first, because when I try you're code I get an error on the set cht (which is normal) which shows event fires normally.
    Also is your workbook set to Automatic calculation ?

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  2. Replies: 3
    Last Post: 09-06-2005, 07:05 AM
  3. Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  4. Replies: 3
    Last Post: 09-06-2005, 04:05 AM
  5. Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1