Why can't my command button perform two functions

[cheoksoon]

Hello,

I'm a total newbie when it comes to VBA. I've created an ActiveX Control button with the following code:

Private Sub Save_Changes_Click()
    Dim findRow As Long
    
    findRow = Application.WorksheetFunction.Match(Range("C3"), ThisWorkbook.Sheets("Patient table").Range("A:A"), 0)
    
    With Worksheets("Patient table")
        .Cells(findRow, 4) = Current_Medications
        .Cells(findRow, 6) = diagnosis_problem_list
             
    End With
    
End Sub

For some reason, the button only works for the first line ".Cells(findRow, 4) = Current_Medications" but not the second line ".Cells(findRow, 6) = diagnosis_problem_list".

Can somebody help point out my mistake?

 

[JoeMo]

Are Current_Medications and diagnosis_problem_list VBA variables? If yes, what's the value of diagnosis_problem_list when you try to execute the second line and what "doesn't work"? Do you get an error message?

 

[cheoksoon]

Hi JoeMo,

Current_Medications and diagnosis_problem_list are both Active X Control Textboxes.

Is that a problem?

By 'not working', I didn't see any error messages. Just that the value in the cells didn't change.

 

[veryamusing]

.Cells(findRow,4) = ActiveSheet.Current_Medications.Text
.Cells(findRow,6) = ActiveSheet.diagnosis_problem_list.Text

 

[cheoksoon]

Hi veryamusing,

Sorry, the code still doesn't work. Clicking the button still does not alter the cell value.

Here's more background:
I'm building a patient database so I couldn't post the excel file here due to confidentiality issues.
The button is placed in a separate worksheet, and I intend it to store information in a another worksheet.
'Current_Medications' and 'diagnosis_problem_list' are both Active X textboxes.
For some strange reason, the code only works for the first line and not the second line, and I have at least 8 more lines of code I want to include.

Can anybody help?

 

[Fluff]

Put

Option Explicit

as the very first line in the sheet module, with your button code & then try clicking the button.
Do you get an error such as
Compile Error:
Variable not defined

 

[cheoksoon]

Put

Option Explicit

as the very first line in the sheet module, with your button code & then try clicking the button.
Do you get an error such as
Compile Error:
Variable not defined

It said "Invalid inside procedure"

 

[Fluff]

It needs to go at the very top of the module, before any code.

 

[cheoksoon]

It needs to go at the very top of the module, before any code.

I placed it at the top of all other sub / private sub codes, and clicked the button.

Nothing Happened.

 

[Fluff]

That's fine, it simply confirms that you hadn't misspelt the name of the textbox.
Couple of other things to check
1) Do you have a Private Sub Worksheet_Change(ByVal Target As Range) event in that module? If so does that look at col F?
2) If you select the cell in col F where the value from the diagnosis_problem_list textbox should go, is there anything in the formula bar?