Userform won't allow for re-entry of data

Adrian86

New Member
Joined
Oct 19, 2006
Messages
22
Hey all, I'm a new poster, humbly in awe of all the knowledge here. I've got some questions, and I'll try and answer questions whenever I can.

I've got a semester project that I'm working on - a mock doctor's office records sheet. There's Excel page with patient information and three buttons to add, edit, or delete a patient.

Whichever one gets clicked, the same userform is generated, with patient information boxes, and two buttons, "Cancel" and "Function." Depending on which button was clicked prior, the function button can perform one of the three requested options. I did this by having the form_initialize sub check to see which action was requested on the Excel page, and then changing the button's caption. Depending on the button's caption, the button's click event changes its control source to one of three blocks of code.

The user can enter information into the patient info boxes, and these boxes get checked to see if they are "in error" - if information has been entered that I don't want. If erroneous information has been entered, I change that boxes' back color to yellow. The problem comes when try to change the erroneous information and resubmit it - the program simply refuses to budge. No errors, clicking the button does nothing.

I think (am probably wrong) this is because the blocks of code to execute are contained in functions, as required by the button's control source. If I try to change them to sub procedures, I get an error stating a function or variable is requried.

Here's where I think the problem lies:

Private Sub btnFunction_Click()
'changes the function of the button depending on which button was clicked on the spreasheet
If btnFunction.Caption = "Add Patient" Then
btnFunction.ControlSource = addpatient1()
ElseIf btnFunction.Caption = "Update Patient" Then
btnFunction.ControlSource = editpatient1()
ElseIf btnFunction.Caption = "Delete Patient" Then
btnFunction.ControlSource = deletepatient1()
End If
End Sub

This set of code will not allow me to change the controlsource to subprocedures, only functions (maybe that's the way it should be, I don't know).

All of this is followed by very lengthy addpatient1, editpatient1, and deletepatient functions, all of which call very lengthy loading and validation procedures. I can post these if anyone would like to see them.

Any help would be much appreciated. Thanks much in advance,

Adrian

Edit:changed the btnfunction.controlsource = "*******" lines to Call Sub "*******" and renamed my functions to subs. Still doesn't work.

Edit again:Fixed it. I had an error variable that I needed to clear at the beginning of each function, otherwise it'd trip over it when trying to resubmit the data. Sorry to waste board space.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,141,096
Messages
5,704,312
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top