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?

 

[cheoksoon]

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?

1. Yes I do!

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$3" Then
    Call refreshEverything
    End If
    
End Sub

2. No, it doesn't have a formula. But because it is on a worksheet called 'Patient Table', so column F is part of a table with a heading called 'Diagnosis'.

(edit: sorry I misunderstood the second question. To reiterate, the cell in Column F where the value from diagnosis_problem_list, contains mainly text data, like "Asthma, depression, Chronic pain". etc.)

 

[Fluff]

Ok the change event, won't be having an effect.
The reason I asked if you could see anything in the formula bar, is that in the past, I have encountered problems where Somebody had changed the font colour to white (or used CF to the same end). It therefore looks like nothing is happening, when it is.

 

[cheoksoon]

Ok, so it's looking like something else is causing the code not to work...

I'm very sure the text is not 'white'.

 

[veryamusing]

This worked for me... I bet you can combine the "separate module" code below with the button click, but I didn't feel like fussing with it more.

For the command button:

Private Sub CommandButton1_Click()
    runThis Range("C3").Value2
End Sub

In a separate module:

Sub runThis(ByVal name As String)
    Dim findRow As Long
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Patient table")
    With ws1
        Dim txt1 As MSForms.TextBox: Set txt1 = .OLEObjects("Current_Medications").Object
        Dim txt2 As MSForms.TextBox: Set txt2 = .OLEObjects("diagnosis_problem_list").Object
        findRow = Application.WorksheetFunction.Match(name, Range("Names"), 0)
        .Cells(findRow, 4) = txt1.Text
        .Cells(findRow, 6) = txt2.Text
    End With
End Sub

 

[cheoksoon]

Thanks everybody for your help.

I've decided to scrap the whole excel spreadsheet and start anew. Strangely, my original code worked like a charm, and so did yours veryamusing.

I'm disappointed that I had to start all over and couldn't troubleshoot the original spreadsheet... sigh. I think it must've been some very tiny thing I did which made the whole thing dysfunctional.

Anyways, I consider the issue solved.

I wish to thank everybody who helped.