Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Building Forms (like a command button) into a cell
Is there an easy way to create a series of command buttons programatically so
that they line up in a column of cells. Each button will need to be renamed, each cell (that gets a button) will need to be sized properly. I would like to know if there is an option that would do this... the only other way I can think of that might work is to figure out how to calculate the necessary size for each cell based on the top-left and bottom-right cords of the button. I also will need to do the same kind of function for a picture-box and assign a formula to this (using it as a view window to show a series of cells from another part of the workbook) Both of these will also include a block copy with a range of cells and their formulae. (If there were a way to do the cell copy [which includes 1 picture box & 1 button] and have it include the objects ... and be able to rename the objects and set the new formula or macro, this would accomplish the task) Any tips on how to approach this? -- Regards, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Building Forms (like a command button) into a cell
Hi John,
Here's an example Sub AddFormsButton() Dim cell As Range Dim oBtn With ActiveSheet For Each cell In Range("B5:B10") Set oBtn = .Buttons.Add(cell.Left, cell.Top, cell.Width, cell.Height) oBtn.Caption = "Button " & cell.Row oBtn.OnAction = "Macro1" Next cell End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "John Keith" wrote in message ... Is there an easy way to create a series of command buttons programatically so that they line up in a column of cells. Each button will need to be renamed, each cell (that gets a button) will need to be sized properly. I would like to know if there is an option that would do this... the only other way I can think of that might work is to figure out how to calculate the necessary size for each cell based on the top-left and bottom-right cords of the button. I also will need to do the same kind of function for a picture-box and assign a formula to this (using it as a view window to show a series of cells from another part of the workbook) Both of these will also include a block copy with a range of cells and their formulae. (If there were a way to do the cell copy [which includes 1 picture box & 1 button] and have it include the objects ... and be able to rename the objects and set the new formula or macro, this would accomplish the task) Any tips on how to approach this? -- Regards, John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Building Forms (like a command button) into a cell
Thats exactly what I was looking for.
Where can I find a list of the available types that will work with the ".add". I need a picture box now. How do I convert the following code to work inside the For each loop? Range("A5:C5").Select 'is the range to show in side the picture Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("H5").Select ' is the cell to position the picture box in ActiveSheet.Pictures.Paste.Select -- Regards, John "Bob Phillips" wrote: Hi John, Here's an example Sub AddFormsButton() Dim cell As Range Dim oBtn With ActiveSheet For Each cell In Range("B5:B10") Set oBtn = .Buttons.Add(cell.Left, cell.Top, cell.Width, cell.Height) oBtn.Caption = "Button " & cell.Row oBtn.OnAction = "Macro1" Next cell End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "John Keith" wrote in message ... Is there an easy way to create a series of command buttons programatically so that they line up in a column of cells. Each button will need to be renamed, each cell (that gets a button) will need to be sized properly. I would like to know if there is an option that would do this... the only other way I can think of that might work is to figure out how to calculate the necessary size for each cell based on the top-left and bottom-right cords of the button. I also will need to do the same kind of function for a picture-box and assign a formula to this (using it as a view window to show a series of cells from another part of the workbook) Both of these will also include a block copy with a range of cells and their formulae. (If there were a way to do the cell copy [which includes 1 picture box & 1 button] and have it include the objects ... and be able to rename the objects and set the new formula or macro, this would accomplish the task) Any tips on how to approach this? -- Regards, John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Building Forms (like a command button) into a cell
Like this???
Option Explicit Sub testme() Dim iRow As Long With ActiveSheet For iRow = 5 To 10 .Cells(iRow, "A").Resize(1, 3).CopyPicture _ Appearance:=xlScreen, Format:=xlPicture .Cells(iRow, "H").Select .Pictures.Paste Next iRow End With End Sub John Keith wrote: Thats exactly what I was looking for. Where can I find a list of the available types that will work with the ".add". I need a picture box now. How do I convert the following code to work inside the For each loop? Range("A5:C5").Select 'is the range to show in side the picture Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("H5").Select ' is the cell to position the picture box in ActiveSheet.Pictures.Paste.Select -- Regards, John "Bob Phillips" wrote: Hi John, Here's an example Sub AddFormsButton() Dim cell As Range Dim oBtn With ActiveSheet For Each cell In Range("B5:B10") Set oBtn = .Buttons.Add(cell.Left, cell.Top, cell.Width, cell.Height) oBtn.Caption = "Button " & cell.Row oBtn.OnAction = "Macro1" Next cell End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "John Keith" wrote in message ... Is there an easy way to create a series of command buttons programatically so that they line up in a column of cells. Each button will need to be renamed, each cell (that gets a button) will need to be sized properly. I would like to know if there is an option that would do this... the only other way I can think of that might work is to figure out how to calculate the necessary size for each cell based on the top-left and bottom-right cords of the button. I also will need to do the same kind of function for a picture-box and assign a formula to this (using it as a view window to show a series of cells from another part of the workbook) Both of these will also include a block copy with a range of cells and their formulae. (If there were a way to do the cell copy [which includes 1 picture box & 1 button] and have it include the objects ... and be able to rename the objects and set the new formula or macro, this would accomplish the task) Any tips on how to approach this? -- Regards, John -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Building Forms (like a command button) into a cell
Yes, thanks Dave.
once more thing though... I want to make the Picture box name something specific. The recorder shows this code... ActiveSheet.Shapes("Picture 39").Select Selection.Name = "ViewBox1" What is the generic way to select a just created shape when you do not know the exact name excel has decided to choose? I tried to use the Selection.name=.... right after .Cells(iRow, "H").Select but that didnt work, no errors.. but the names were still "picture##-picture##" The Cells in Col-H got the assigned names. Also tried .Shapes.Select (just leaving out the explicit name) but that caused an error. Finally I tried: Dim iRow As Long Dim Count As Integer Count = 0 With ActiveSheet For iRow = 5 To 10 Count = Count + 1 .Cells(iRow, "A").Resize(1, 3).CopyPicture _ Appearance:=xlScreen, Format:=xlPicture .Cells(iRow, "H").Select .Pictures.Paste .Pictures.Select Selection.Name = "ViewBox" & Count Next iRow End With The first time through the loop, the name "ViewBox1" does get assigned properly, however the 2nd time through the loop a runtime error 438 (object doesn't support the property or method) occurs on the "Selection.Name = "ViewBox" & Count" statement. This confuses me, why would the assignment work once but not again??? -- Regards, John "Dave Peterson" wrote: Like this??? Option Explicit Sub testme() Dim iRow As Long With ActiveSheet For iRow = 5 To 10 .Cells(iRow, "A").Resize(1, 3).CopyPicture _ Appearance:=xlScreen, Format:=xlPicture .Cells(iRow, "H").Select .Pictures.Paste Next iRow End With End Sub John Keith wrote: Thats exactly what I was looking for. Where can I find a list of the available types that will work with the ".add". I need a picture box now. How do I convert the following code to work inside the For each loop? Range("A5:C5").Select 'is the range to show in side the picture Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("H5").Select ' is the cell to position the picture box in ActiveSheet.Pictures.Paste.Select -- Regards, John "Bob Phillips" wrote: Hi John, Here's an example Sub AddFormsButton() Dim cell As Range Dim oBtn With ActiveSheet For Each cell In Range("B5:B10") Set oBtn = .Buttons.Add(cell.Left, cell.Top, cell.Width, cell.Height) oBtn.Caption = "Button " & cell.Row oBtn.OnAction = "Macro1" Next cell End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "John Keith" wrote in message ... Is there an easy way to create a series of command buttons programatically so that they line up in a column of cells. Each button will need to be renamed, each cell (that gets a button) will need to be sized properly. I would like to know if there is an option that would do this... the only other way I can think of that might work is to figure out how to calculate the necessary size for each cell based on the top-left and bottom-right cords of the button. I also will need to do the same kind of function for a picture-box and assign a formula to this (using it as a view window to show a series of cells from another part of the workbook) Both of these will also include a block copy with a range of cells and their formulae. (If there were a way to do the cell copy [which includes 1 picture box & 1 button] and have it include the objects ... and be able to rename the objects and set the new formula or macro, this would accomplish the task) Any tips on how to approach this? -- Regards, John -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Building Forms (like a command button) into a cell
Sub AA()
Dim iRow As Long Dim Count As Integer Dim pic as Picture Count = 0 With ActiveSheet For iRow = 5 To 10 Count = Count + 1 .Cells(iRow, "A").Resize(1, 3).CopyPicture _ Appearance:=xlScreen, Format:=xlPicture .Cells(iRow, "H").Select .Pictures.Paste For Each pic In .Pictures If pic.TopLeftCell.Row = iRow Then pic.Name = "ViewBox" & Count End If Next Next iRow End With End Sub -- regards, Tom Ogilvy "John Keith" wrote in message ... Yes, thanks Dave. once more thing though... I want to make the Picture box name something specific. The recorder shows this code... ActiveSheet.Shapes("Picture 39").Select Selection.Name = "ViewBox1" What is the generic way to select a just created shape when you do not know the exact name excel has decided to choose? I tried to use the Selection.name=.... right after .Cells(iRow, "H").Select but that didnt work, no errors.. but the names were still "picture##-picture##" The Cells in Col-H got the assigned names. Also tried .Shapes.Select (just leaving out the explicit name) but that caused an error. Finally I tried: Dim iRow As Long Dim Count As Integer Count = 0 With ActiveSheet For iRow = 5 To 10 Count = Count + 1 .Cells(iRow, "A").Resize(1, 3).CopyPicture _ Appearance:=xlScreen, Format:=xlPicture .Cells(iRow, "H").Select .Pictures.Paste .Pictures.Select Selection.Name = "ViewBox" & Count Next iRow End With The first time through the loop, the name "ViewBox1" does get assigned properly, however the 2nd time through the loop a runtime error 438 (object doesn't support the property or method) occurs on the "Selection.Name = "ViewBox" & Count" statement. This confuses me, why would the assignment work once but not again??? -- Regards, John "Dave Peterson" wrote: Like this??? Option Explicit Sub testme() Dim iRow As Long With ActiveSheet For iRow = 5 To 10 .Cells(iRow, "A").Resize(1, 3).CopyPicture _ Appearance:=xlScreen, Format:=xlPicture .Cells(iRow, "H").Select .Pictures.Paste Next iRow End With End Sub John Keith wrote: Thats exactly what I was looking for. Where can I find a list of the available types that will work with the ".add". I need a picture box now. How do I convert the following code to work inside the For each loop? Range("A5:C5").Select 'is the range to show in side the picture Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("H5").Select ' is the cell to position the picture box in ActiveSheet.Pictures.Paste.Select -- Regards, John "Bob Phillips" wrote: Hi John, Here's an example Sub AddFormsButton() Dim cell As Range Dim oBtn With ActiveSheet For Each cell In Range("B5:B10") Set oBtn = .Buttons.Add(cell.Left, cell.Top, cell.Width, cell.Height) oBtn.Caption = "Button " & cell.Row oBtn.OnAction = "Macro1" Next cell End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "John Keith" wrote in message ... Is there an easy way to create a series of command buttons programatically so that they line up in a column of cells. Each button will need to be renamed, each cell (that gets a button) will need to be sized properly. I would like to know if there is an option that would do this... the only other way I can think of that might work is to figure out how to calculate the necessary size for each cell based on the top-left and bottom-right cords of the button. I also will need to do the same kind of function for a picture-box and assign a formula to this (using it as a view window to show a series of cells from another part of the workbook) Both of these will also include a block copy with a range of cells and their formulae. (If there were a way to do the cell copy [which includes 1 picture box & 1 button] and have it include the objects ... and be able to rename the objects and set the new formula or macro, this would accomplish the task) Any tips on how to approach this? -- Regards, John -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Building Forms (like a command button) into a cell
Another way if these are the only pictures on the sheet.
Option Explicit Sub testme01() Dim iRow As Long Dim pic As Picture With ActiveSheet For iRow = 5 To 10 .Cells(iRow, "A").Resize(1, 3).CopyPicture _ Appearance:=xlScreen, Format:=xlPicture .Cells(iRow, "H").Select .Pictures.Paste Set pic = .Pictures(.Pictures.Count) pic.Name = "ViewBox" & .Pictures.Count Next iRow End With End Sub It uses the fact that the picture you just pasted is the last picture on the worksheet. John Keith wrote: Yes, thanks Dave. once more thing though... I want to make the Picture box name something specific. The recorder shows this code... ActiveSheet.Shapes("Picture 39").Select Selection.Name = "ViewBox1" What is the generic way to select a just created shape when you do not know the exact name excel has decided to choose? I tried to use the Selection.name=.... right after .Cells(iRow, "H").Select but that didnt work, no errors.. but the names were still "picture##-picture##" The Cells in Col-H got the assigned names. Also tried .Shapes.Select (just leaving out the explicit name) but that caused an error. Finally I tried: Dim iRow As Long Dim Count As Integer Count = 0 With ActiveSheet For iRow = 5 To 10 Count = Count + 1 .Cells(iRow, "A").Resize(1, 3).CopyPicture _ Appearance:=xlScreen, Format:=xlPicture .Cells(iRow, "H").Select .Pictures.Paste .Pictures.Select Selection.Name = "ViewBox" & Count Next iRow End With The first time through the loop, the name "ViewBox1" does get assigned properly, however the 2nd time through the loop a runtime error 438 (object doesn't support the property or method) occurs on the "Selection.Name = "ViewBox" & Count" statement. This confuses me, why would the assignment work once but not again??? -- Regards, John "Dave Peterson" wrote: Like this??? Option Explicit Sub testme() Dim iRow As Long With ActiveSheet For iRow = 5 To 10 .Cells(iRow, "A").Resize(1, 3).CopyPicture _ Appearance:=xlScreen, Format:=xlPicture .Cells(iRow, "H").Select .Pictures.Paste Next iRow End With End Sub John Keith wrote: Thats exactly what I was looking for. Where can I find a list of the available types that will work with the ".add". I need a picture box now. How do I convert the following code to work inside the For each loop? Range("A5:C5").Select 'is the range to show in side the picture Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("H5").Select ' is the cell to position the picture box in ActiveSheet.Pictures.Paste.Select -- Regards, John "Bob Phillips" wrote: Hi John, Here's an example Sub AddFormsButton() Dim cell As Range Dim oBtn With ActiveSheet For Each cell In Range("B5:B10") Set oBtn = .Buttons.Add(cell.Left, cell.Top, cell.Width, cell.Height) oBtn.Caption = "Button " & cell.Row oBtn.OnAction = "Macro1" Next cell End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "John Keith" wrote in message ... Is there an easy way to create a series of command buttons programatically so that they line up in a column of cells. Each button will need to be renamed, each cell (that gets a button) will need to be sized properly. I would like to know if there is an option that would do this... the only other way I can think of that might work is to figure out how to calculate the necessary size for each cell based on the top-left and bottom-right cords of the button. I also will need to do the same kind of function for a picture-box and assign a formula to this (using it as a view window to show a series of cells from another part of the workbook) Both of these will also include a block copy with a range of cells and their formulae. (If there were a way to do the cell copy [which includes 1 picture box & 1 button] and have it include the objects ... and be able to rename the objects and set the new formula or macro, this would accomplish the task) Any tips on how to approach this? -- Regards, John -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Forms Command Button - Removal | Excel Discussion (Misc queries) | |||
Use of Forms Command-button on Worksheet | Excel Discussion (Misc queries) | |||
Command button on forms | Excel Discussion (Misc queries) | |||
Change Caption of Forms Command Button | Excel Programming | |||
how do you hide a forms command button | Excel Programming |