Cell specific attributes automatically applied on input text

[Azmordeus]

Hi guys,
I hope the title isn’t extremely complicated but I will explain in any case what I need and what I want to do.

To clarify all the attributes I want are cell-specific so they should ONLY apply to those i want to customise.

The first attribute I want is to put a restriction on a cell that doesn’t allow me to go beyond a specific number of digits or even better an option that highlights the extra digits in case I write more digits than I’m supposed to in that specific cell.

The second one is also a cell restriction that denies suggesting other numbers already present in the spreadsheet or alternatively forcing the maximum number inside it to not be higher than a specific number.

The next one is a date specific cell that can somehow check today’s date in the system and show tomorrow’s date. ( I know it must be impossible to implement, it might not even be possible actually)

The last one is making a small macro that deletes specific cells in the spreadsheet in one click.

I hope I didn’t blow your mind.
If you reached this far you definitely

[Wanderer]

@Azmordeus

The second one is also a cell restriction that denies suggesting other numbers already present in the spreadsheet

I remember I created a setup for shipping lists, where validity suggested only numbers from a list (announced for shipping) and suppressing the values already used. Method was also using a formula for data-validity.

[Wanderer]

@Azmordeus

The last one is making a small macro that deletes specific cells in the spreadsheet in one click.

Depending on “specific”: Have you tried just recording this.
(You can’t delete cells, but their contents.)

[joshua4]

By ‘delete’ a cell, do you mean remove the content, or do you mean some sort of cell removal where other cells slide left/up to fill in the space?

If you mean the former, here is a macro created by Gemini that needed just a little adjustment to do the job. [oCellRange did not get reset to Nothing, and LO BASIC required the () for If Not (…Is Object) Then…]

Sub DCBAAddress()
	DeleteCellsByAddress("Sheet1",Array("A1","B2","C3:C4","1sjdflasjdflksjdf;lk1B1","B3"))
End Sub

Sub DeleteCellsByAddress(sSheetName As Variant, sCellAddresses As Variant)
    Dim oDoc As Object
    Dim oSheet As Object
    Dim oCellRange As Object
    Dim sAddress As String
    Dim i As Integer

    Rem Get the current document
    oDoc = ThisComponent

    Rem Get the active sheet
    oSheet = oDoc.Sheets.getByName(sSheetName)

    Rem Loop through the array of cell addresses
    For i = LBound(sCellAddresses) To UBound(sCellAddresses)
        sAddress = sCellAddresses(i)

        Rem Try to get the cell range from the address
        On Error Resume Next Rem Handle potential errors if an address is invalid
        Set oCellRange = oSheet.getCellRangeByName(sAddress)
        On Error GoTo 0 Rem Reset error handling

        Rem If the cell range was found, clear its contents       
        If Not (oCellRange Is Nothing) Then
        	xray oCellRange
            oCellRange.clearContents(1+2+4+8+16) Rem Clear all types of contents (text, values, formulas, formats, notes)
        Else
            MsgBox "Warning: Cell address '" & sAddress & "' not found or invalid. Execution is halting.", _
                   vbExclamation, "Invalid Cell Address"
        	Exit For
        End If
        oCellRange = Nothing
    Next i
End Sub

[Azmordeus]

Yeah, I’m so sorry. I meant their contents. You’re correct.

[karolus]

@Azmordeus

The next one is a date specific cell that can somehow check today’s date in the system and show tomorrow’s date. ( I know it must be impossible to implement, it might not even be possible actually)

this one is pretty simple:

=TODAY()+1

[mikekaganski]

@Azmordeus

restriction on a cell that doesn’t allow me to go beyond a specific number of digits or even better an option that highlights the extra digits in case I write more digits than I’m supposed to in that specific cell

Using a formula like

=A1=ROUNDSIG(A1;5)

you may check if the number of significant digits in the cell is no greater than 5. Or was your “digits” not for “significant”, but maybe for “digits in the fractional part”? Then it would be like

=A1=ROUND(A1;5)

for 5 digits after decimal separator.

The formula can be used in Validity feature, to reject the invalid input or warn about it; or you may use conditional formatting to highlight the whole cells that don’t match your condition. But without using some macros, it’s impossible to highlight only some extra digits in the problematic cells.