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.
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.
