UserForm Issues

[Maria]

Hello - I am new to programming in VB - I am using Excel 2003. I am mostly
reading some Excel help books and Searching on the internet for example code.

Characteristics of userform:
- I have a UserForm that displays properly when excuting the Macro in the
Workbook (which only has 1 worksheet)
- I have only 2 Option Buttons set in a Frame -- the values CAN be selected
(one at a time) -- Option Button Names: OpBtnIVA, OpBtnIVAExento
- I have an OK button - which is NOT working properly - I get an error of
"1004" when clicked -- Button Name: ButtonOK
- I also have code for the UserForm Cancel Control - which IS working
properly.

Results Expected:
1. When selecting OpBtnIVA then Cell D41 will be populated with D39*0.16
(computed value)
Note: D41 is a combined range of cells (D41, D42, E41 and E42) but when
selecting the range (in Excel) the cell is listed as D41.
2. When selecting OpBtnIVAExento then Cell D41 will be populated with the
text EXENTO
Note: D39 is a combined range of cells (D39, D40, E39 and E40) but when
selecting the range (in Excel) the cell is listed as D39

I have tried the following 2 codes in my button but either code is not
functioning.

1st Button Code:

Private Sub ButtonOK_Click()

Dim x As Control

For Each x In Frame1.Controls
If x.Value = "OpBtnIVA" Then
Range(“D41â€).Value = Range("D39") * 0.16
Unload FormIVA

ElseIf x.Value = "OpBtnIVAExento" Then
Range(“D41â€).Value = "EXENTO"
Unload FormIVA

End If
Next

End Sub


2nd Button Code:

Private Sub ButtonOK_Click()

If OpBtnIVA Then Cells(41, D).Value = Cells(39, D).Value * 0.16
If OpBtnIVAExento Then Cells(41, D).Value = "EXENTO"
Unload FormIVA

End Sub

If you need anything else, please indicate. All help is greatly appreciated.

thank you in advance
Maria

 

[Tom German]

Maria,

Here are two ways that may work for you. Note: "Me" represents the userform
where the code block is located.

Private Sub ButtonOK_Click()
'''Determine which optionbutton is selected.
If Me.OpBtnIVA.Value = True Then
ThisWorkbook.ActiveSheet.Range("D41").Value = Range("D39") * 0.16
Unload FormIVA
ElseIf Me.OpBtnIVAExento.Value = True Then
ThisWorkbook.ActiveSheet.Range("D41").Value = "EXENTO"
Unload FormIVA
Else
MsgBox "Please select one of the options"
End If
End Sub


-- OR --


Private Sub ButtonOK_Click()
'''Determine which optionbutton is selected.
Select Case True
Case Me.OpBtnIVA.Value
ThisWorkbook.ActiveSheet.Range("D41").Value = Range("D39") * 0.16
Unload FormIVA
Case Me.OpBtnIVAExento.Value
ThisWorkbook.ActiveSheet.Range("D41").Value = "EXENTO"
Unload FormIVA
Case Else
MsgBox "Please select one of the options"
End Select
End Sub

 

[Maria]

Tom

Thank you - it did work for a couple of hours. Now I am getting an error
message for the following line:

ThisWorkbook.ActiveSheet.Range("D41").Value = Range("D39") * 0.16

Is there another method of referencing the cell D41?
Or I am wondering if maybe I have not declared the workbook right. The
following code is in my Workbook_Open declaration:

Private Sub Workbook_Open()
'this automatically opens the UserForm when the excel workbook is opened
FormIVA.Show

End Sub






ThisWorkbook.ActiveSheet.Range("D41").Value = Range("D39") * 0.16

 

[Maria]

Hello Tom:

Sorry - I know what the issue is -- I have cell D41 Protected.

This brings another question. Can I use VBA to unprotect the sheet, write
in the response to D41 and then protect the sheet again?

Thank you for all your help
Maria

 

[Tom German]

Maria,

There are a couple of approaches.
(1) UserInterfaceOnly property
(2) Turn Off Protection / Make changes / Turn On Protection
I tend to use Option 2 the most, due to the variety of changes I'm trying to
make to a worksheet.

-----------------------------------------
Option 1
-----------------------------------------
You can use the .Protect method of the Worksheet object to turn On the
protection for a worksheet.
The UserInterfaceOnly property can be set to True to allow the VBA code to
modify the contents of a protected worksheet, while the user will not be
able to do so.

It is important that the subProtectSheet routine is run at least once
before any other code attempts to modify the contents of the worksheet. The
UserInterfaceOnly property needs to get set to True. It's ok if you manually
turn On the worksheet protection via the menubar: Tools | Protection |
Protect Sheet... Just be sure to use the same Password as the one in the
subProtectSheet routine.

Sub subProtectSheet()
'''IMPORTANT: This routine must be run at least once before any other
''' VBA code attempts to modify the worksheet. If the worksheet
''' was manually protected by the developer, then the password
''' used must be the same as the one below.

'''Turn the worksheet protection On for the active worksheet.
'''Allow VBA to modify the contents of the worksheet
(UserInterfaceOnly:=True).
ActiveSheet.Protect _
Password:="mysecret", _
Contents:=True, _
DrawingObjects:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True

End Sub


Sub subTest1()

'''VBA will attempt to modify the contents of the cell
'''on a worksheet (that is likely protected).
ActiveSheet.Range("A1").Value = 456

End Sub


-----------------------------------------
Option 2
-----------------------------------------
Alternatively, you can use VBA code to turn Off the worksheet protection,
modify a cell, turn On the worksheet protection. Once again, if you manually
turn On the worksheet protection, you need to use the same password in the
VBA code.

Sub subChangeCellValue()

'''Turn Off the protection.
subWorksheet_Protect wsTemp:=ActiveSheet

'''Modify the worksheet.
ActiveSheet.Range("A1").Value = 456

'''Turn On the protection.
subWorksheet_Unprotect wsTemp:=ActiveSheet

End Sub

Sub subWorksheet_Protect(wsTemp As Worksheet)
wsTemp.Protect _
Password:="mysecret", _
Contents:=True, _
DrawingObjects:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
End Sub

Sub subWorksheet_Unprotect(wsTemp As Worksheet)
wsTemp.Unprotect _
Password:="mysecret"
End Sub

 

[Maria]

Tom

I tried Option 1 and it worked PERFECTLY

I would like to thank you for all your help, suggestions and patience with
my issue. I just hope I can as well help others in the manner that you
helped me.

Thank you again and have a GREAT day
Maria

 

[Maria]

Tom

Thank you -- Option 1 worked perfectly.

Again thank you for all your help - without it I would have been searching
on the interent for awhile.

Again, Thank you and have a GREAT day
Maria