Add checkbox

[raphagcwill]

Hi there,
wondering if you can help to change the VAB I wil post below so that it only adds a checkbox if the imediate cell to it's left contains text. If no text is found not checkbox should be inserted.

Thanks in advance


Sub InsertCheckBoxes()
'Update 20140506
Dim Rng As Range
Dim WorkRng As Range
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Ws = Application.ActiveSheet
Application.ScreenUpdating = False
For Each Rng In WorkRng
With Ws.CheckBoxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height)
.Characters.Text = Rng.Value
End With
Next
WorkRng.ClearContents
WorkRng.Select
Application.ScreenUpdating = True
End Sub

 

[ChrisM]

Sub InsertCheckBoxes()
'Update 20140506
Dim Rng As Range
Dim WorkRng As Range
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Ws = Application.ActiveSheet
Application.ScreenUpdating = False
For Each Rng In WorkRng
   
  If Rng.Offset(0,1) <> "" Then
     With Ws.CheckBoxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height)
          .Characters.Text = Rng.Value
     End With
  End If

Next
WorkRng.ClearContents
WorkRng.Select
Application.ScreenUpdating = True
End Sub

 

[raphagcwill]

Hello ChrisM,

thanks for you prompt reply.

I have made the changes you suggested, but the macro adds a checkbox even for those cells with no text in it.

It might be worth saying that the the wording on the column will always be "PO, "PPO" and "SETTLE".
Any blank row, or cell wth no wording in it should be left out


Tanks for you time

 

[ChrisM]

Hmm, well you can certainly have cells that look empty but are not, so maybe that is why the <> "" test is failing.

Easy enough to flip it around the other way:

If Rng.Offset(0,1) = "PO" OR Rng.Offset(0,1) = "PPO" OR Rng.Offset(0,1) = "SETTLE" Then
     With Ws.CheckBoxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height)
          .Characters.Text = Rng.Value
     End With
  End If

 

[raphagcwill]

Bingo..it does work flawlessly now.

Since I will copy this vba into a master macro, i would prefer not to have to choose the rage as the message box require.

Would t be easy to espeify a rage and add a last row function?

The checkboxes will alway be insert on column Y

 

[ChrisM]

Yes that would be easy, but I'm not sure what your range would be. Y(x) : Y(z)

What are the row numbers for "x" and "z"? One is the LastRow right? Which one? How long (tall) is the range?

 

[raphagcwill]

I understand.

let's see If can exlaing it


My range is A:X

On X column i have the wording "po", "ppo", "settle"starting from x:2 down to maybe 100 (it may vary)

 

[ChrisM]

You need a reference column, between A and W, that you can guarantee won't have any blank values. Once you decide that column (say it's A for now), your code is:

Dim lRow as Long

lRow = ActiveSheet.Range("A1").End(xlDown).Row

Set WorkRng = ActiveSheet.Range("X2:X" & lRow)

 

[raphagcwill]

Thanks a million for your help and patient. I will do as you suggested.
Have a nice evening

 

[raphagcwill]

Hi Chris...

just wanted to thank you again. I have changed it succesfully!

Regards,
Raphael