Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issues
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issues
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issues
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 "Tom German" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issues
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 "Maria" wrote: 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 "Tom German" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issues
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issues
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 "Tom German" wrote: 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issues
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 "Maria" wrote: 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 "Tom German" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Issues with UserForm Box | Excel Programming | |||
Font Issues when using UserForm box after clicking a Checkbox | Excel Discussion (Misc queries) | |||
Userform/Textbox Issues | Excel Programming | |||
Userform/TextBox Issues | Excel Programming | |||
Excel UserForm issues: Textboxes cannot be blank | Excel Programming |