Excel Visual Basic Code to get ToggleButtons and OptionButtons to return Yes or No instead of True or False

[mebanet]

I have an Excel UserForm that I use to enter data from my job. I commission solar inverter stations. Each row in my spread sheet contains data for a different station. On my UserForm I have a CommandButton, that when press takes the data from the current row being displayed in the UserForm and writes it to a fillable PDF. On this UserForm I have ToggleButtons and OptionButtons that return for selecting different options. The problem is these buttons return True or False to their respective cells and Adobe Acrobat needs Yes or NO for the Checkboxes that correspond to these cells.

Here's an example of one of my OptionButton groups:
1. There are 3 Option Buttons named zI, zJ and zK captioned “SMA CoMVPP” “SMA MVPP” and “CUSTOMER SUPPLIED” respectively.
2. Option Button zI will enter data in to Cell I for the current row I am entering data in ith the UserForm, zJ will enter into J and zK will enter into K.
3. Right now if I check zI, “True” will be entered into Cell I and “False” will be entered into Cells J and K, I need those to be "Yes" or "No".

Here's an example of one of my ToggleButton setups.
1. There are 4 Toggle Buttons named zY, zZ, zAA and zAB, captioned "Wiring", "User", "Install" and "Keys" respectively
2. Toggle Button zY will enter data into Cell Y for the current row I am entering data in with the UserForm, zZ, zAA and zAB will enter into A, AA and AB respectively
3. Right now if I click zY, “True” will be entered into Cell Y and if I unclick it "False will be entered, the same thing applies for the other Toggle Buttons and there respective cells.

I have search code on this site and countless other Excell and VBA forums but can't get any of the code to work on my UserForm, it's probably me... doesn't seem like it should be that hard.

 

[Andrew Poulsom]

Welcome to MrExcel.

To do that you would need to use the Control's Click event procedure rather than a ControlSource. You can test that its Value property is True and if so write Yes in the cell, otherwise No.

 

[mebanet]

Andrew, I understand the concept of what I need to do I just can't figure out how to do it.


Private Sub zY_Click()

(Everything I've entered here either still returns a true or false or give me an error)

End Sub

A friend helped me with the code I'm about to list, it enters Yes and No when the Save Button is pressed, but the problem with it is that my Get Data isn't working right now. If I use my "Previous" or "Next" command button and the row contains data then my UserForm will populate with the data. The Option/Toggle buttons however will be grayed out if that row has Yes or No entered instead of True and False.

Private Sub Save_Click()
For j = 1 To v
col = Replace(Cells(1, j).Address(0, 0), "1", "")

If j = 9 Or j = 10 Or j = 11 Or j = 25 Or j = 26 Or j = 27 Or j = 28 Then
If Me.Controls("z" & col).Value = False Then
Cells(r, j) = "NO"
Else
Cells(r, j) = "YES"
End If
Else
Cells(r, j) = Me.Controls("z" & col).Value
End If
Next j
RowNumber = r

End Sub

Here is my Get Data Sub

Private Sub GetData()


Save.Enabled = True
New1.Enabled = True
Prev.Enabled = True
Next1.Enabled = True
First.Enabled = True
Last.Enabled = True
Cells(r, 1).Select
Go_To.Visible = False


For j = 1 To v
col = Replace(Cells(1, j).Address(0, 0), "1", "")
Me.Controls("z" & col).Value = Cells(r, j)
Next j
RowNumber = r


End Sub