To simplify use of a spreadsheet and avoid anyone just adding or deleting rows as they wish (and therefore messing up the formulae) I have used a series of userforms. The first Userform gives the user the option to either 'Cancel', 'Delete Row', or 'Insert Row'. Selecting 'Cancel' unloads the form, and selecting 'Delete Row' unloads the original form and shows another Userform - this allows the user to select the row number they want to delete, and pressing OK deletes it. This all works fine.
The issue I have is with the 'Insert Row' button - this should unload the first Userform and show another userform. This new 'Insert Row' Userform allows the user to input the row number after which the new row is to be inserted. The user has the option to input further detail in various textboxes and comboboxes which will populate the new empty row - these include start and end dates.
I was having integer vs string issues with the option for manual input of the dates, so I opted to go with another 2 userforms, opened from the start and end dates which gave a calendar (select the date you want, press OK and the date is inserted in the relevant textbox in the 'Insert Row' userform). I tested these all the way through to iron out the wrinkles, and everything seemed to be working.
I then opened the first userform from the command button on the spreadsheet,
Selected 'Cancel' and 'Delete Row' to check they still worked, then selected 'Insert Row' and got 'Runt-time error '1004' Application-defined or object-defined error'. When I went to debug, it highlights the line: userform1.show
The only thing I can think of that could be a problem (and the only significant change I've made) are the two calendars, which reference userform1. I've carried out multiple searches, but haven't found anything which even looks like my problem - I just don't know what the issues are with having multi-layered userforms. I've included some of the code by way of further explanation:
Private Sub InsertRowButton_Click() 'unload the first userform when the user selects "Insert Row" Unload ModifyProjectUserForm1 ''This line highlights as the problem UserForm1.Show End Sub '''the following subs are embedded in "UserForm1" (the one to Insert Row), allowing the user to select a date: Private Sub InsertRowNumberStartDateCommandButton_Click() frmCalendar1.Show End Sub Private Sub InsertRowNumberEndDateCommandButton_Click() frmCalendar2.Show End Sub '''The following are the subs used to take the dates and place them in "UserForm1": Private Sub StartDateCalendarOK_Click() On Error Resume Next UserForm1.InsertRowNumberStartDateTextBox.Value = frmCalendar1.Calendar1.Value Unload frmCalendar1 End Sub Private Sub EndDateCalendarOK_Click() On Error Resume Next UserForm1.InsertRowNumberEndDateTextBox.Value = frmCalendar2.Calendar2.Value Unload frmCalendar2 End Sub
I expected the 'Insert Row' userform to open, allowing me to put a new row in, with associated data, and dates. What I got was a runtime error, and I'm at a complete loss.
I don't know if I'm going down completely the wrong route, but I'm loathe to post my entire code, because there's a lot of it!
Can anyone help?