none
Occassional Method 'Range' of object '_Global' failed when using userform RRS feed

  • Question

  • Hello all,

    I have created a tool in Excel, in which we can enter scores for a questionaire that customers can fill in. 
    Our team can choose to use either the Excel sheet itself or a userform to mark the answers.

    In case the score isn't high enough, the tool generates a code that can be kept in the customer's file, and when the customer comes back with additional answers, there is a button on both the Excel sheet and the userform that opens a different userform where the code can be pasted, and this will then mark the previous answers again, so that we don't have to check everything from the start.

    This all works perfect, apart from in one scenario.

    For people using the userform to mark the answers, if they click the button to enter a code, and then they for whatever reason first go into a different Excel file before pasting the code, when clicking the submit button it gives the error Method 'Range' of object '_Global' failed. 

    When using the Excel sheet instead of the Userform, this issue does not happen, and when not first going into a different Excel file there is also no problem.

    The line of code where the error occurs is the last line in this section of the code:

    Private Sub CommandButton1_Click()
     'check if code is correct length
     'first remove leading or trailing whitespace (spaces and tabs)
     CodeEntry.Value = Trim(CodeEntry.Value)
     Do Until Not Left(CodeEntry.Value, 1) = Chr(9)
      CodeEntry.Value = Trim(Mid(CodeEntry.Value, 2, Len(CodeEntry.Value) - 1))
     Loop
     Do Until Not Right(CodeEntry.Value, 1) = Chr(9)
      CodeEntry.Value = Left(CodeEntry.Value, Len(CodeEntry.Value) - 1)
     Loop
     If (CodeEntry.TextLength <> "16") Then
      Label1.Caption = "Invalid code, try again"
      CodeEntry.Value = ""
     Else
     
     Range("code!Newcode").Value = CodeEntry.Value

    Anyone an idea why this happens and how to prevent this from happening? The chance that it will affect our users is slim, but because the chance exists I rather have a solution before I roll out this version of the tool.

    Thanks,
    Pascal

    Monday, April 8, 2019 5:39 PM

Answers

  • Re:  still getting error using range name

    Why not use the actual range address, such as, B10:M100.
       Range("B10:M100") will work on the ActiveSheet (only).
       If the range must refer to a particular worksheet in a particular workbook then use...
       Workbooks("MyStuff").Worksheets("Sludge").Range("B10:M100")

    • Marked as answer by PascalStil Tuesday, April 23, 2019 6:03 PM
    Wednesday, April 17, 2019 7:23 PM
  • Hi Pascal,

    If I do understand your explanation correctly: the user can call the form and then switch to another workbook?

    Why not using a modal form? That the user can switch to another workbook/sheet indicates that the userform is modeless.

    Alos, it is preferrable to use Sheets("xxx").Range("yyy")="zzz"

    But all this still refers to the activeworkbook. As Nothing Left to Lose suggested, use the full qualified path.

    An idea could be to use some global variables: workbook, sheet & range and initialize them when calling the userform so that you always can refer back to the original items.

    The parent method can also be used:

    - Given Range("x")

    - Range("x").Parent.Name refers to the sheet

    - Range("x").Parent.Parent.Name refers to the workbook

    Anyway, the runtime error indicates that the code doesn't find your reference in the activeworkbook, so you must make sure that the code knows to what you are referring.

    HTH

    • Marked as answer by PascalStil Tuesday, April 23, 2019 6:03 PM
    Thursday, April 18, 2019 10:03 AM

All replies

  • While I had no issues when working directly from the Excel sheet instead of the userform previously, while running some further tests after posting the above, I suddenly also got the error from there, so it seems it may not be related to the userform.

    I also like to highlight that there exists no reference "code!Newcode" in the other Excel file, so that can't be a cause of a conflict.

    Monday, April 8, 2019 5:58 PM
  • Re:  error sometimes

    Does "codeNewcode" contain a sheet reference?
    Tuesday, April 9, 2019 1:53 AM
  • Yes, code is the sheet, and Newcode is the name I gave the field B14 on that sheet.
    I thought that the exclamation mark indicated it's a sheet reference.
    • Edited by PascalStil Tuesday, April 9, 2019 4:59 PM
    Tuesday, April 9, 2019 4:57 PM
  • re:  error sometimes

    Suggest you prefix "Range" with the sheet name...

    Worksheets("RealName").Range("code!Newcode").Value = CodeEntry.Value


    Wednesday, April 10, 2019 1:22 AM
  • If I put it in as 

    Worksheets("Code").Range("code!Newcode").Value = CodeEntry.Value

    and I use the same steps as which gave the initial error, it now gives the error "Run-time error '9': Subscript out of range"

    I initially thought I had to enter the filename somewhere, but checking https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa224506(v=office.11) showed that I just use the name of the tab which is also already indicated in the Range, so I have the feeling something may be going wrong by using the combination of Worksheet and Range in this way.

    If I change it to 

    Worksheets("code").Range("Newcode").Value = CodeEntry.Value

    an idea I got after reading https://docs.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/subscript-out-of-range-error-9, the same error occurs. 

    Any further help would be appreciated.


    • Edited by PascalStil Wednesday, April 17, 2019 5:28 PM
    Wednesday, April 17, 2019 5:13 PM
  • Re:  still getting error using range name

    Why not use the actual range address, such as, B10:M100.
       Range("B10:M100") will work on the ActiveSheet (only).
       If the range must refer to a particular worksheet in a particular workbook then use...
       Workbooks("MyStuff").Worksheets("Sludge").Range("B10:M100")

    • Marked as answer by PascalStil Tuesday, April 23, 2019 6:03 PM
    Wednesday, April 17, 2019 7:23 PM
  • Hi Pascal,

    If I do understand your explanation correctly: the user can call the form and then switch to another workbook?

    Why not using a modal form? That the user can switch to another workbook/sheet indicates that the userform is modeless.

    Alos, it is preferrable to use Sheets("xxx").Range("yyy")="zzz"

    But all this still refers to the activeworkbook. As Nothing Left to Lose suggested, use the full qualified path.

    An idea could be to use some global variables: workbook, sheet & range and initialize them when calling the userform so that you always can refer back to the original items.

    The parent method can also be used:

    - Given Range("x")

    - Range("x").Parent.Name refers to the sheet

    - Range("x").Parent.Parent.Name refers to the workbook

    Anyway, the runtime error indicates that the code doesn't find your reference in the activeworkbook, so you must make sure that the code knows to what you are referring.

    HTH

    • Marked as answer by PascalStil Tuesday, April 23, 2019 6:03 PM
    Thursday, April 18, 2019 10:03 AM
  • Hello Nothing Left to Lose and JP, 

    thanks for your suggestions. 
    Today was quite a busy day, so I haven't had time to test this yet, I will get back to this after the Easter weekend.

    I can already clarify that I use a name for the range instead of the 'normal' range, because that makes it easier for someone else who may need to do updates in the future to understand what exactly is happening. 

    That there is the option to switch to a different workbook is required, because our team needs to use Excel for other tasks as well and I had to specifically enable the option to switch focus to a different workbook to ensure other tasks wouldn't be affected by using this tool.

    Thanks,
    Pascal

    Thursday, April 18, 2019 5:11 PM
  • Thanks guys,

    the suggestions here helped me also find https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa224504(v=office.11) where it pointed out the ThisWorkbook property. With some further help through a different channel I quickly found how to implement this and it seems to be resolved now.

    I still have some further tests to run, but I think we can close this case as resolved and if I run into further problems I probably have to create a separate post for that.

    Once again thanks for all your help everyone!

    Pascal

    Tuesday, April 23, 2019 6:03 PM