VBA TOGGLE BUTTON - info to excell

[rjgtavares]

hello,
i have a toggle buttn in vba to control if the product is "ok" or "not ok" but after i send the info for excel it doesn't show in excel as "ok" or "not ok"... how can i do that?

thank you

 

[sykes]

Hi, and welcome to Mr. Excel.

I'm afraid that you'll need to explain your architecture in more detail, before anyone can help with some suggestions; is the toggle button on a worksheet, or a userform? Where do you expect the "OK/NOT OK" to appear - in a cell?
Bear in mind that people reading your post (and willing to help you, if they can) have never seen your workbook, so they have no idea whatsoever what it all looks like - names of sheets, names of userforms etc, and what your "product" is etc etc.

 

[rjgtavares]

I am creating a form in vba and i would like the form information to be transfered to an excel sheet (data).
i have managed to transfer all the data but i created a toggle button in vba, and i want that the information passed to the cell in excel sheet was:
if the button is pressed: "not ok"
if the button is not pressed: "ok"

sorry

than you again

 

[rjgtavares]

i tryed this

Private Sub ToggleButton10_Click()
If ToggleButton10.Value = True Then
Me.ToggleButton10.Caption = "NÃO CONFORME"
Else
Me.ToggleButton10.Caption = "CONFORME"
End If
End Sub

but on excel cell shows True or False

 

[sykes]

OK. We still don't have any of your sheet names, or "form" name (I assume it's a UserForm?) or ToggleButton name, so I can only give you some general code, then you'll need to incorporate it into your project.

My suggestion is that your use the Togglebutton's caption to display it's state to the user, and also output the caption string into your worksheet cell.

Set the value of the Toggle to True, when the userform initialises:

Private Sub UserForm_Initialize()
Me.ToggleButton1.Value = True
End Sub

Then, when the button's clicked by the user, change the caption, thus:

Private Sub ToggleButton1_Click()

With Me.ToggleButton1
If .Value = False Then
   .Caption = "Not OK"
Else: .Caption = "OK"
End If
End With

End Sub

Finally, incorporate an extra line into the code which you've written to transfer all of your data from the form, onto the worksheet, which takes the caption from your button, and transfers it into the required cell:

Sheets("Sheet1").Range("A1").Value = Me.ToggleButton1.Caption

Just to demonstrate it all working, this changes the value of cell A1, each time the button is clicked:

Private Sub ToggleButton1_Click()

With Me.ToggleButton1
If .Value = False Then
   .Caption = "Not OK"
Else: .Caption = "OK"
End If
End With

Sheets("Sheet1").Range("A1").Value = Me.ToggleButton1.Caption
End Sub

...obviously you don't want the cell text changing each time the togglebutton's pressed, you only want it to happen when all your other data is transferred to the sheet (by whatever method you use for that) but I just wanted to demo my idea in use.

 

[sykes]

Sorry - I didn't see your third post, before I posted my answer!

 

[rjgtavares]

well it changed the button caption, but in excel only apears "true" or "false"
what i want is that instead of showing "true" show "ok" and instead of "false" show "not ok" but in the excel cell

 

[sykes]

What method are you using, to try to get the worksheet cell to display ""NÃO CONFORME" or "CONFORME"?

Please post all of your code, so that we're not having to guess...

If we know how you're passing the information to the cell, we should be able to help, but only if you tell us the whole situation.

 

[rjgtavares]

Private Sub CommandButton1_Click()
    Dim LastRow As Long
    
    LastRow = Worksheets("DADOS").Cells(Worksheets("DADOS").Rows.Count, 1).End(xlUp).Row + 1
    
    Cells(LastRow, 1).Value = Me.txtdesignação
    Cells(LastRow, 2).Value = Me.ComboBox6
    Cells(LastRow, 3).Value = Me.CheckBox1
    Cells(LastRow, 4).Value = Me.ComboBox7
    Cells(LastRow, 5).Value = Me.txtdata
    Cells(LastRow, 6).Value = Me.Txtoperador
    Cells(LastRow, 7).Value = Me.txtespessura_nominal
    Cells(LastRow, 8).Value = Me.txtcomprimento_nominal
    Cells(LastRow, 9).Value = Me.ComboBox1
    Cells(LastRow, 10).Value = Me.ComboBox2
    Cells(LastRow, 11).Value = Me.TXTATADO
    Cells(LastRow, 12).Value = Me.txtlote_cliente
    Cells(LastRow, 13).Value = Me.Txtdiametro1
    Cells(LastRow, 14).Value = Me.txtdiametro2
    Cells(LastRow, 15).Value = Me.txtdiametro3
    Cells(LastRow, 16).Value = Me.txtdiametro4
    Cells(LastRow, 17).Value = Me.txtespessura
    Cells(LastRow, 18).Value = Me.txtcomprimento
    Cells(LastRow, 19).Value = Me.txtretitude
    Cells(LastRow, 20).Value = Me.ToggleButton10
    
    
End Sub

that is the way i am transferring the data. so the info must go to the last row and to the 20th column

 

[sykes]

You need to make the cell value equal to the togglebutton10 caption, not just the ToggleButton:

Private Sub CommandButton1_Click()
    Dim LastRow As Long
    
    LastRow = Worksheets("DADOS").Cells(Worksheets("DADOS").Rows.Count, 1).End(xlUp).Row + 1
    
    Cells(LastRow, 1).Value = Me.txtdesignação
    Cells(LastRow, 2).Value = Me.ComboBox6
    Cells(LastRow, 3).Value = Me.CheckBox1
    Cells(LastRow, 4).Value = Me.ComboBox7
    Cells(LastRow, 5).Value = Me.txtdata
    Cells(LastRow, 6).Value = Me.Txtoperador
    Cells(LastRow, 7).Value = Me.txtespessura_nominal
    Cells(LastRow, 8).Value = Me.txtcomprimento_nominal
    Cells(LastRow, 9).Value = Me.ComboBox1
    Cells(LastRow, 10).Value = Me.ComboBox2
    Cells(LastRow, 11).Value = Me.TXTATADO
    Cells(LastRow, 12).Value = Me.txtlote_cliente
    Cells(LastRow, 13).Value = Me.Txtdiametro1
    Cells(LastRow, 14).Value = Me.txtdiametro2
    Cells(LastRow, 15).Value = Me.txtdiametro3
    Cells(LastRow, 16).Value = Me.txtdiametro4
    Cells(LastRow, 17).Value = Me.txtespessura
    Cells(LastRow, 18).Value = Me.txtcomprimento
    Cells(LastRow, 19).Value = Me.txtretitude
    Cells(LastRow, 20).Value = Me.ToggleButton10.Caption
    
    
End Sub