1

I'm very new to Excel VBA and can't seem to figure out why I keep getting error 80010108 "Method 'Value' of object 'Range' failed." If anyone can help me out it would be very much appreciated!!

I created a UserForm ("MainView") with a command button ("Add New"), which brings up another UserForm ("AddNewWorkOrder") that contains another command button ("Enter"). I want this Enter command button to enter all data typed in this userform's textboxes into the corresponding columns in the active worksheet. **I need to add rows to a table in the worksheet

Now when I run MainView and then click "Add New" to bring up AddNewWorkOrder, I'm then unable to enter the data because clicking "Enter" will throw up the error 80010108 and crash Excel (prompted to restart Excel).

HOWEVER if I run the AddNewWorkOrder UserForm directly, without going through MainView, it will enter the data exactly as needed.

This is the code I have for MainView's "Add New":

Private Sub AddNew_Click()

Me.Hide
AddNewWorkOrder.Show

End Sub

This is the code I have for "Enter" (including just one textbox for simplicity):

Private Sub EnterCommandButton_Click()

Range("A" & Rows.Count).End(xlUp).Select

HoldVal = ActiveCell.Value + 1
MsgBox HoldVal
ActiveCell.Offset(1, 0).Value = HoldVal

ActiveCell.Offset(1, 1).Select
ActiveCell.Value = Me.ItemSizeTextBox

Me.Hide
MainView.Show

End Sub

Edit: the line "ActiveCell.Offset(1, 0).Value = HoldVal" is where I'm getting an error

EvaWii
  • 11
  • 3
  • 1
    Are you wanting to check `A1` only? You're using `ActiveCell`, first try if replacing that with `WorkOrderNumbers.Range("A1")` and see if it works like you expect. If so, I can go into more detail why, but see [How to avoid `.Select`\`.Activate`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – BruceWayne Apr 11 '16 at 19:54
  • @BruceWayne in column A, I have numbers increasing by +1. I was trying to select the last cell in column A, then enter into the cell below it the previous number +1 – EvaWii Apr 11 '16 at 20:00
  • 3
    Find the last row as shown [Here](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) and then directly write to it. For example `WorkOrderNumbers.Range("A" & LastRow).Value = "Blah Blah"` – Siddharth Rout Apr 11 '16 at 20:03
  • @SiddharthRout I don't seem to have any problems finding the last row. Using my code above, i'm able to enter data through my AddNewWorkOrder userform into the worksheet. I run into a problem when I try running my MainView userform first, and then opening the AddNewWorkOrder userform via a command button in MainView – EvaWii Apr 11 '16 at 20:21
  • Show the code for MainView's AddNew button – Sorceri Apr 11 '16 at 21:15
  • @Sorceri i've added it above – EvaWii Apr 11 '16 at 21:22
  • 4
    *I don't seem to have any problems finding the last row* - sure. Except you say yourself you're very new to VBA, and your code is awfully inefficient. Take 5 minutes and read @SiddharthRout's link for finding the last row. Then thank him when you have 42000 rows to iterate before you get to the last one ;-) – Mathieu Guindon Apr 11 '16 at 21:38
  • You call the value method three times, once checking to see if it is empty, once to assign holdVal and again to assign ItemSizeTextBox. On Which of these three lines is the error occurring? – Sorceri Apr 11 '16 at 22:34
  • @Mat'sMug So, i edited my code (see above). Works perfectly when I run AddNewWorkOrder. However, I still get an error when trying to first run MainView – EvaWii Apr 11 '16 at 22:35
  • @Sorceri I'm not sure.. this is exactly what happens: i run MainView, click "Add New" to bring up AddNewWorkOrder in AddNewWorkOrder, i click "Enter" i immediately get an 80010108 message (it took me a couple times to even jot down the error because) i'm then immediately prompted to restart Excel. – EvaWii Apr 11 '16 at 22:41
  • @EvaWii You need to use the debugger. Put break points in on each line and then you can see where it fails. – Sorceri Apr 11 '16 at 22:42
  • @EvaWii on a side note, I took your code and it runs just fine. May want to clear out temp directories and reboot then run it again. – Sorceri Apr 11 '16 at 22:45
  • @Sorceri thank you! Will update when I see any changes – EvaWii Apr 11 '16 at 22:47
  • Would you mind changing `Unload Me` to `Me.Hide` and let us know if you get the same error. – Ambie Apr 11 '16 at 22:52
  • @Ambie Yep, still the same error – EvaWii Apr 11 '16 at 22:56
  • I would almost guarantee that this is a problem with [using ActiveCell instead of a hard reference](http://stackoverflow.com/q/10714251/4088852). Put the line `MsgBox ActiveCell.Parent.Name & "!" & ActiveCell.Address` right below `Range("A" & Rows.Count).End(xlUp).Select`. Is the address it shows the correct Worksheet and cell address? – Comintern Apr 12 '16 at 01:09
  • Okay, and would you also put `Application.EnableEvents = False` at the start of your Sub and `Application.EnableEvents = True` at the end of the Sub (ie check you're not entering an infinite loop). @Comintern, I'm tempted to agree except that OP's complaining of an Excel crash which sounds more like some kind of infinite loop. – Ambie Apr 12 '16 at 05:03
  • @Comintern yes the correct worksheet and cell address was shown, however i was still given an error – EvaWii Apr 12 '16 at 12:54
  • @Ambie i tried both yours and Comintern's suggestions, together and separately, unfortunately neither worked – EvaWii Apr 12 '16 at 12:54

0 Answers0