Problem with UserForm and Formulas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

WOW....that's everything I wanted, and a little bit more. It's so good it feels wrong to tell you that the Add button doesn't work. Update is fine, as are all the other command buttons, formulas, etc. But when I click Add nothing happens and I can't see why in the code.

The only other thing is the number formatting. The only boxes I need 2 decimal places in are the ones in the Ratings frame - % of Turnover. Actually I need these to show as percentages, at the moment they are showing the results of the calculation rather than the percentage value. For example, in the first record the % of turnover is 1.97% however on the form it is showing as 0.02, which is the value if the format of the cell was Number rather than Percentage.

FYI when I tested the form in the sample file I would get a run-time error 1004: application-defined or object-defined error when using the navigation buttons - First, Next etc. However, when I copied the coding to my working file it no longer gives me this error.

Many thanks for all you help.

Oh.. maybe I uploaded the wrong version of the file. The Add button was working for me. Will check. Actually, it seemed to me you don't need the "Add" button at all, and could achieve the same with the "Clear" and "Update" buttons on their own.

Percentages should be easy to fix. Will revert.

ASKER

Yeah I see what you mean about Add, Update and Clear, and I did consider that when designing the form, it's just that keeping them as separate actions makes the process clear for the user....if you see what I mean. The user might get confused if the Add and Update are the same button and could mistakenly update a record when they think they are adding a new one if they didn't clear the form first.

Yes. Actually there is a danger they will manually change the URN number and then hit update, which would over-write the wrong record, I think.

The run-time error with the nav buttons was caused by your test entry of "the quick brown fox" etc in row 4. The code was trying to read that cell's formula and crashing because there is a 255-character limit on formula length, and your test cell was 31,000 characters long. I'm just adding some protective code for that.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Numbers and percentage in Rating all perfect....but oh no, Add still isn't working for some reason.

I also got a run-time error 13: type mismatch when I reach record number 15 using the Next button. Debug highlights the following line:

If Len(rCell.Value) < 256 Then

However, when I copied the code to the working file the error didn't occur...phew.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Add button is still not working for me. The result of my debugging attempt is below.

(Now before your palm hits your face, let me just say that I haven’t used VBA in years so my skills are not just rusty they’ve almost fossilised, so too are my debugging skills.)

Break at:
If Me.txtURN.Value = "" Then
Me.txtURN.Value = Application.Max(rData.Columns(1)) + 1
currentrow = rData.Rows.Count + 1
UpdateRecord currentrow
'adjust size of rData
Set rData = Range("A1").CurrentRegion
End If

Immediate window results:
print currentrow
2
print rData.Rows.Count
23
print range("A1")
URN

Break at:
CreateControlArray
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
If WorksheetFunction.CountIf(ws.Columns(1), Me.txtURN) > 0 And txtURN <> "" Then
MsgBox "The record for this URN already exists in the database." & vbNewLine & _
"Please hit Update, or Clear form before adding a new record.", vbCritical, "URN Already Exists!"
Exit Sub

Immediate window results:
print lr
0
print Rows.Count
1048576
print xlUp
-4162
print Me.txtURN.Value
23
print currentrow
2
print iCol
81

Break at:
Sub CreateControlArray()

Immediate window results:
print iCol
81
print UBound(aControls)
80
print iCol = UBound(aControls)
False
print sControls
DUMMY0,txtURN,txtCoName,txtCoNos,cboLegal,cboStatus,txtSICCode,txtOthSIC,txtSICDesc,txtTradeDesc,txtMaterials,txtSNos,txtAddL1,txtAddL2,txtAddL3,txtCounty,txtPcode,txtTelNosM,txtEmailM,txtWeb,chkTPS,chkCTPS,txtContact,txtPosition,txtTelNosC,txtEmailC,txtCAdd,txtFFloor,txtFDesc,txtSAAlink,txtFNotes,cboLA,txtDeal,txtEUse,txtEofTO,txtERating,txtRUse,txtRofTO,txtRRating,txtAcDate,txtEmpLAY,txtEmp20,txtEmp19,txtEmp18,txtEmp17,txtEmp16,txtEmp15,txtEmp14,txtEmp13,txtEmp12,txtHeadcount,txtTOLAY,txtTO20,txtTO19,txtTO18,txtTO17,txtTO16,txtTO15,txtTO14,txtTO13,txtTO12,txtBalLAY,txtBal20,txtBal19,txtBal18,txtBal17,txtBal16,txtBal15,txtBal14,txtBal13,txtBal12,txtNosSites,txtSites,txtOrgCat,cboIsSub,cboIsParent,txtNosSubs,cboOrgType,txtMedSME,cboSMERevised,txtNotes

Not sure where to go from here so thought I would give you an update and maybe you could point me in the right direction.

Thanks.

um. If you step through the routine, after the break, pressing F8 repeatedly, does it exit at some point before doing anything useful?

Also, if you press "add" when there is already a record in the form, does it issue the correct warning?

ASKER

No, it seems to step through everything ok, nothing untoward seems to happen other than not being able to add a record.

The record already exists warning is working fine.

does it crash? If it steps through everything, what happens when it gets to the line where it adds the data?

ASKER

I put a break at CreateControlArray in Sub EditAdd() and opened the form to add data. When I pressed Add VBA opens and I pressed F8 stepping through until it reaches the End Sub line of cmdAdd_Click(), then the yellow highlight disappears with the cursor at the start of the line and the form still open.

If I go back to the form and click Add again it switches back to VBA with cmdAdd_Click() highlighted in yellow. Pressing F8 again steps through the whole thing until it reaches the End Sub line of cmdAdd_Click() again with the form still open.

Nothing crashes and nothing gets added to the data.

Are you actually adding anything to the form before hitting the "add" button? The following lines should be adding whatever you have in the form to your worksheet

Me.txtURN.Value = Application.Max(rData.Columns(1)) + 1
    currentrow = rData.Rows.Count + 1
    UpdateRecord currentrow

Open in new window

That is they set the "URN" control to the value of the large existing URN + 1, set the row to the next row after the rData range and update the record into that row. Worth checking that currentrow is being given the value you expect.

I am genuinely baffled, since the code is essentially the same as what you had in the original workbook and works with no problem for me. I sent you a private message.

ASKER

OH MY GAWD I didn't spot it was actually assigning the next URN for the user....I was typing the next number in the box...It's actually works.

I'm SO sorry for being such a doofus, told you my VBA skils were rusty.

I'll copy the code into my working file and test it there, but I don't think there should be any more problems. Hopefully this is job done.

Many, many thanks, and once again sorry for being a doofus.

ASKER

Hi, finished testing the form in my working file and everything is working splendidly - yeah. Well everything except the 2 checkboxes I have on the second page of the form.

For some reason, for all records they are checked and greyed out, which is odd as they are not disabled or locked and the default value should be false (unchecked). I also noticed that when I Add or Update the checkboxes instead of TRUE or FALSE being entered in the cells on the data sheet it inputs 0 for False and -1 for True.

Very baffling.

No worries, re the "add" button. I don't THINK I changed its behaviour, but maybe I did :)

Re the "true" and false" - minus 1 and zero are secretly the values of true and false (if you convert a boolean true/false to a number explicitly that is what you will get).

We could write some code to make the form export the words instead quite easily.

Are the checkboxes greyed out when you hit "add", or just when you click on a record. If so, that is probably the result of the "loadboxes" routine trying to interpret the words "true" and "false" as numbers. I will fix and upload something in a minute.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Perfect. Tested it on the sample worksheet then copied the code to my working file - everything is working perfectly.

As soon as the user guide is finished and worksheets protected and/or hidden this job will be done.

I can't thank you enough for all your help, not only have you solved all my problems I have learned a lot and hopefully my rusty VBA skills are a little less rusty.

Many many thanks.

My pleasure. I'm glad we sorted it out.

ASKER

If this forum had a Star Rating system I would give you 5 Gold Stars.

Your solutions and your patience with my rusty skills have been invaluable; I couldn’t have done it without you.

Many thanks