Posts by adamhodgkins

adamhodgkins · Apr 14th 2016

Re: VBA to set properties of checkbox to 'unlocked'

Hi,

I'm trying to get it to function so that users aren't able to unprotect the sheet, and leave only the areas where the text and checkboxes will be added unlocked. Unprotecting the sheet makes everything work again, so maybe this is a limitation of Excel?

adamhodgkins · Apr 14th 2016

Re: VBA to set properties of checkbox to 'unlocked'

Hi Stephen,

I'm getting the following error:

Run-time error '1004':
Unable to get the Add property of the CheckBoxes class

I've added your code into the function but the same error is coming up

adamhodgkins · Apr 14th 2016

Re: VBA to set properties of checkbox to 'unlocked'

I've found that by unlocking all cells on the sheet then protecting it and running the code to add a new text entry and checkbox, it's stopping and the following line of code is highlighted in yellow...

Code

Set cb = ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)

Does anyone know where I'm going wrong here?

Thanks,
-Adam

adamhodgkins · Apr 14th 2016

Hi all,

I have some code that members here helped with a while ago, this is attached to a small userform and it inputs text into a cell with a checkbox in the cell to the right:

Code

With Worksheets("Sheet1")
        lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
        .Cells(lastrow, "B").Value = Me.txtServiceName.Text
         
        With .Cells(lastrow, "C")
            Set cb = ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)
        End With
        With cb
            .Characters.Text = ""
            .Value = xlOff
             'Link to col D
            .LinkedCell = "$D$" & lastrow
            .Display3DShading = False
        End With
    End With

Display More

I'm working on protecting the sheet and I think I'm having problems because I've discovered that checkboxes are locked by default.

Is there any way that when checkboxes are added with the above code, that the properties can be set so that they are unlocked?

Thanks,
-Adam

adamhodgkins · Apr 13th 2016

Re: How to delete checkboxes in a specific cell range

Hi, that worked perfectly!

Thanks very much for your help!!

Adam

adamhodgkins · Apr 13th 2016

Re: How to delete checkboxes in a specific cell range

Hi Stephen,

Thanks very much for the response! I've just tried that and it's coming up with an error:

Run-time error '1004':
Method 'Intersect' of object '_Global' failed

When I click Debug it highlights the following line in yellow:

If Not Intersect(c.TopLeftCell, Range("C21:C600,I21:I600")) Is Nothing Then

adamhodgkins · Apr 13th 2016

Hi all,

I currently have a piece of code that deletes all checkboxes on a worksheet:

Code

For Each c In Sheets("Sheet1").CheckBoxes
c.Delete
Next

I'm trying to get it to delete checkboxes only in 2 specific cell ranges (C21:C600) and (I21:I600), but I can't figure out how to do this.

I've tried adding ".Range..." after the sheet name in the statement but it's not being recognised...

Is there a way to delete checkboxes in these specific ranges and leave all others on that sheet in place?

Any help would be greatly appreciated!!

Thanks,

adamhodgkins · Mar 31st 2016

Re: Macro to merge cells based on the number of items in a list

To try and explain better, if the following cells had text:

Cell A2: Hello
Cell A3: Everyone

I'm looking for a macro to take those and make

Cell C4: Hello, Everyone

adamhodgkins · Mar 31st 2016

Re: Macro to merge cells based on the number of items in a list

Hi, either the list items all in one cell, or the list items in separate cells with the cells in columns to the left being merged down, i.e. if there ends up being 5 items in the list, there should be 5 cells merged down in the columns to the left.

Apologies for the poor description!

adamhodgkins · Mar 31st 2016

Hi all,

I'm in the process of creating an archiving function for a spreadsheet and I'm a bit stuck. I've created a button and macro to record information in specific cells into a table, and each time the button is pressed the entries are recorded on the next row down - this part works fine.

What I can't figure out how to do is to get items of a user populated list (could be 1 item, could be 20...) into the table and then auto merge & center the cells to the left.

It's a bit difficult to explain so I've attached the spreadsheet - on the Archive tab, the table from rows B:F is where I've got to, and the look of the table from rows N:R is what I'm trying to achieve.

Any help / suggestions would be greatly appreciated!!

Thanks,
-Adam

adamhodgkins · Jan 19th 2016

Re: Delete information from multiple locations

Hi,

I've logged into the Excel Forum to try and delete the posts but I'm unable to, apologies again.

Do you know if it's possible to tweak the script above so that removal of multiple entries is possible?

Thanks,
-Adam

adamhodgkins · Jan 19th 2016

Re: Delete information from multiple locations

Apologies for cross posting - I'll review the rules and remove the other posts in a minute.

I've tried the code above and it works really well for removing one entry at a time, but when removing multiple entries at once it only removes one of the entries in the config sheet, is there a way to adjust the script to be able to remove multiple values at the same time?

Thanks,
-Adam

adamhodgkins · Jan 19th 2016

Re: Delete information from multiple locations

Hi Stephen,

I'm not overly familiar with how to use the Find command, previously I've only used it to check whether a value exists in a list of items, do you know how I could go about integrating that into my workbook to delete text?

adamhodgkins · Jan 19th 2016

Hi all, I've got a couple of macros set up to do the following:

* A button to add text and with checkbox next to it then copy the data to another 'config' sheet
* A button to remove the text and checkbox if the checkbox is selected and the 'remove' button is clicked

So far when the 'remove' button is clicked the text and checkbox are deleted from the main sheet, but I can't figure out how to get that button to delete the relevant data in the config sheet...

I thought about just deleting all information in the config sheet but the problem with that is if a list of items is built up in the 'Name' column (service dependencies sheet) and some are removed, the remaining items will automatically move up the page to remove blank spaces - so another challenge is getting the data in the config sheet to do the same thing (remove the relevant entries and shift the remaining ones up the page).

I've attached the workbook I'm working on rather than pasting all of the code, any help / advice would be greatly appreciated!

Thanks,
Adam

adamhodgkins · Nov 27th 2015

Re: Function to remove checkboxes if they're selected

Hi Ger,

Thanks for your help! Which bit of the code needs to be changed to delete from the bottom up?

Thanks,
-Adam

adamhodgkins · Nov 27th 2015

Re: Function to remove checkboxes if they're selected

Hi Mike, that's exactly what I'm looking for - thanks very much for your help!!

adamhodgkins · Nov 27th 2015

Re: Function to remove checkboxes if they're selected

Hi, that's awesome, thanks very much for your help! Is there a way to tweak it slightly so that multiple checkboxes and the associated information are removed at the same time? In section 2 of the spreadsheet you sent back with 2 options selected the remove button deletes them both, but with all 3 selected only 2 get removed?

adamhodgkins · Nov 27th 2015

Hi all,

At the moment I've got 2 sections within a worksheet with an 'Add' button in each to add text and a checkbox which works fine. What I'm trying to accomplish is when certain checkboxes within each section are selected they can be removed by clicking the 'Remove' button then have the cells in those columns deleted to bring the contents up.

The code for adding the text and checkboxes is:

Section 1:

Code

Private Sub Submit1_Click()




    Dim lastRow As Long
    Dim cb As Object
        
    With Worksheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
        .Cells(lastRow, "B").Value = Me.txt1.Text
         
        With .Cells(lastRow, "C")
            Set cb = ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)
        End With
        With cb
            .Characters.Text = ""
            .Value = xlOff
            .LinkedCell = "$D$" & lastRow
            .Display3DShading = False
        End With
    End With




End Sub

Display More

Section 2:

Code

Private Sub Submit2_Click()




   Dim lastRow As Long
    Dim cb As Object
        
    With Worksheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
        .Cells(lastRow, "F").Value = Me.txt2.Text
         
        With .Cells(lastRow, "G")
            Set cb = ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)
        End With
        With cb
            .Characters.Text = ""
            .Value = xlOff
            .LinkedCell = "$H$" & lastRow
            .Display3DShading = False
        End With
    End With

Display More

I've attached the document for reference, any help would be greatly appreciated!!!

Thanks,
-Adam

adamhodgkins · Nov 25th 2015

Re: Excel VBA to check for incomplete entries on userform

Managed to figure it out, there needs to be a 'Then' at the end of the If statement

adamhodgkins · Nov 25th 2015

Re: Excel VBA to check for incomplete entries on userform

Hi, thanks for the quick response! I've tried putting that code in at the top of the userform code but there seems to be an error, the 'If comboServiceClass.ListIndex = 1' and 'MsgBox "Incomplete Data"' lines are coming up in red text. When I try to run the form it's coming back with:

Compile error:
Syntax error

Is there anything else I need to put into the code to make it work?

Thanks for the heads up with posting code too

Adam