Setting up a Userform

[rhino4eva]

So i work in a laboratory , we currently have data that comes out in a specific non numerical order. I Cant sort these positions as there are fixed.
We that have to do 1of 4 tests on these specimens. Not all specimens get all tests, its kind of cherry picking. The request forms we receive are in numerical order.
So here's the thing . I can design a userform to have a number box and four tick boxes. What i'm not sure about is how to search in column "A" for a the 7 digit lab number and transfer the tick correct selection from the userform to the master sheet

i don't know whether its a vlookup in the vba or a match/index search i need

any help would be gratefully recieved

 

[dmt32]

Hi,
just a generalized approach you could take

Private Sub CommandButton1_Click()
    Dim txtLabNo        As Variant, m As Variant
    Dim i               As Long
    Dim wsMaster        As Worksheet
   
    'lab number
    txtLabNo = Me.txtLabNumber
    If Not IsNumeric(txtLabNo) Then Exit Sub Else txtLabNo = Val(txtLabNo)
   
    'change master sheet name as required
    Set wsMaster = ThisWorkbook.Worksheets("Sheet1")
   
    'search column A
    m = Application.Match(txtLabNo, wsMaster.Columns(1), 0)
   
    If Not IsError(m) Then
       
        For i = 1 To 4
       
            'post to master sheet
            wsMaster.Cells(CLng(m), i + 1).Value = Me.Controls("CheckBox" & i).Value
           
        Next i
       
    Else
   
        'inform user
        MsgBox txtLabNo & Chr(10) & "Record Not Found", 48, "Not Found"
       
    End If
   
End Sub

You will need to adapt to meet your own specific project need but if still have issues, post code you are using & sample of worksheet using MrExcel Addin: XL2BB - Excel Range to BBCode - plenty here to offer guidance.

Dave

 

[rhino4eva]

Getting a debug
what is

wsMaster.CELLS(CLng(m), i + 1).Value = Me.Controls("CheckBox" & i).Value


Upload Image

 

[dmt32]

Getting a debug
what is

wsMaster.CELLS(CLng(m), i + 1).Value = Me.Controls("CheckBox" & i).Value

can you specify the error?

Dave

 

[rhino4eva]

ok so i have figured out that i am stupid and un-named my check boxes
rather than "TRUE" of "FALSE" id like "x" or " " in the master table

 

[dmt32]

ok so i have figured out that i am stupid and un-named my check boxes
rather than "TRUE" of "FALSE" id like "x" or " " in the master table

As stated, code is just a general idea of how you could develop a solution but needs to be adapted to meet your specific need.

To post an alternative to a boolean value you could modify code like this

'post to master sheet
            With Me.Controls("CheckBox" & i)
                wsMaster.Cells(CLng(m), i + 1).Value = IIf(.Value, Choose(i, "W", "X", "Y", "Z"), "")
            End With

again, just a suggestion - you will need to adapt to meet specific project need.

Dave

 

[rhino4eva]

As stated, code is just a general idea of how you could develop a solution but needs to be adapted to meet your specific need.

To post an alternative to a boolean value you could modify code like this

'post to master sheet
            With Me.Controls("CheckBox" & i)
                wsMaster.Cells(CLng(m), i + 1).Value = IIf(.Value, Choose(i, "W", "X", "Y", "Z"), "")
            End With

again, just a suggestion - you will need to adapt to meet specific project need.


Cheers Dave ...... nearly there ... just need "X" in every column

 

[rhino4eva]

i rate your programming very highly, so much so mid like to implement it further but don't know how to adapt it.

i have a different spread sheet id like too add just one "x" in a column that relates to the text box number search

 

[dmt32]

with a new question should start a new thread

Dave

 

[rhino4eva]

not really a new question. more of a need to understand your great code in able to adjust it to further my knowledge