Password with some Extras

English
,
#1

The problem with the code lies in the way it handles cell referencing, password validation, and copying formulas. This VBA code is designed to unlock certain cells in a spreadsheet after validating a master password and copying formulas from column C to column B. However, the code has a few issues that need attention for proper functionality.

First, the cell referencing is done using getCellByPosition, which works correctly for identifying specific rows and columns. However, the password verification is based on the value in D2, which corresponds to position (3,1), but this is not the correct reference for D2. The position (3,1) refers to column B, row 2, not D2. This needs to be fixed to refer to (3, 2) for D2.

Second, the process of copying formulas from column C to B using Formula works fine as long as the intended functionality is to preserve formulas. However, if the goal is to copy only values, Value should be used instead of Formula. Additionally, there’s a potential issue in how the loop is set to iterate from rows 3 to 100, but it may not take into account the specific range where data is located. It’s important to verify that the range is correct and that columns B and C are properly referenced.

The code also uses IsProtected to lock or unlock cells, but the actual unlocking and locking mechanism may not work if the sheet protection settings are not properly configured beforehand.

Lastly, the Password Fade away macro assigns the string “TOP SECRET” to column B, which could be misleading if not handled properly.

In summary, the code needs adjustments in how cells are referenced, how formulas are copied, and ensuring proper protection settings. Careful debugging and testing of the range and conditions would be necessary to ensure smooth functionality.

i need some help…

#2

The position of the cells is zero based:
A1 → getCellByPosition(0, 0)
D2 → getCellByPosition(3, 1)

#3

You can Get/Set the cell contents without usage the Copy/Paste function of the operating system.

oCell2.Value = oCell1.Value
oCell2.String = oCell1.String
oCell2.Formula= oCell1.Formula
oCell2.FormulaLocal = oCell1.FormulaLocal

#4

…

Sorry, but currently I don’t see any code posted/attached/linked.
.
Also you tagged base, basic. May I suggest Calc, Basic instead, as I don’t see any relations to the database-module named “Base”.

#5 
Sub PasswortFreischalten()
    Dim oSheet As Object
    Dim oCell As Object
    Dim MasterPass As String
    Dim i As Integer

    MasterPass = "123456" ' Das MasterPasswort
    oSheet = ThisComponent.Sheets(0)
    oCell = oSheet.getCellByPosition(2, 2) ' #Zelle D2 ist Position (3, 1) '

    ' #ĂśberprĂĽfen, ob das eingegebene Passwort korrekt ist'
    If oCell.String = MasterPass Then
        ' #Schleife von C3 (3. Zeile) bis C100 (100. Zeile)'
        For i = 3 To 100 ' #Passwörter befinden sich in den Zellen C3 bis C100'
            oSheet.getCellByPosition(2, i).IsProtected = False ' # Entsperren der Zellen in Spalte B'
            oSheet.getCellByPosition(2, i).String = oSheet.getCellByPosition(3, i).Formula ' #Kopieren der Formeln von Spalte C nach B'
        Next i
    Else
        MsgBox "Falsches Passwort!", 16, "Zugriff verweigert"
    End If
End Sub

Sub PasswortVerstecken()
    Dim oSheet As Object
    Dim i As Integer

    oSheet = ThisComponent.Sheets(0)

    ' # Schleife von C3 (3. Zeile) bis C100 (100. Zeile)'
    For i = 3 To 130
        oSheet.getCellByPosition(2, i).String = "TOP SECRET" ' # Verbergen des Werts in Spalte B'
        oSheet.getCellByPosition(2, i).IsProtected = True ' #Sperren der Zellen in Spalte B'
    Next i
End Sub
#6

Again: use zero based indexes in the StarBasic

C3:C130 → .getCellByPosition(2, i) ; where i must be 2 to 129 (or from 2 to 99 - if you want use the rows 3…100 only)
.
.
oCell = oSheet.getCellByPosition(2, 2) ' Zelle D2 ist Position (3, 1)
Which cell you want to get really? (3,1) or (2,2) ?
.
.
Your code is not VBA!
VBA: Microsoft’s Visual Basic for Applications.
Your code is written in (Star)Basic.
And the code uses the API functions of the LibreOffice
API: Application Programming Interface

#7

May I ask: Is this question the only text written by yourself and the rest the answer of an AI?
EDIT: Using your username as a hint may answer this directly:

.
And you try to port some code from VBA to LibreOffice? Or is this a new project?

#8

The position of the Column B is 1 (in the StarBasic, ant in the API) (The Column A is 0). The cell object has not property IsProtected . Check it in XrayTool or in the MRI.
The Cells have PROPERTY OBJECT named .CellProtection. And the CellProtection object has .IsLocked property.
.
Therefore the valid line is:
oSheet.getCellByPosition(1, i).CellProtection.IsLocked = False ' # Entsperren der Zellen in Spalte B

#9

Here is an another version of your code:

REM  *****  BASIC  *****
Option Explicit

Sub PasswortFreischalten()

 Dim oSheet, oMpwCell, oCellInB, oCellInC As Object
 Dim MasterPassword As String
 Dim i As Integer

	MasterPassword = "123456" ' Das MasterPasswort
	oSheet = ThisComponent.Sheets().getByIndex(0)
	oMpwCell = oSheet.getCellByPosition(3, 1) ' Zelle D2 ist Position (3, 1)

	' ĂśberprĂĽfen, ob das eingegebene Passwort korrekt ist
	If oMpwCell.String = MasterPassword Then
    	oSheet.Unprotect(MasterPassword)
    	For i = 2 To 99 ' Passwörter befinden sich in den Zellen C3 bis C100
    		oCellInB = oSheet.getCellByPosition(1, i)
    		oCellInC = oSheet.getCellByPosition(2, i)
			oCellInB.CellStyle = "Visible"
			oCellInB.Value = oCellInC.Value
		Next i
	Else
		MsgBox "Falsches Passwort!", 16, "Zugriff verweigert"
	End If

End Sub
'___________________________________________________________________________


Sub PasswortVerstecken()

 Dim oSheet, oCellInB, oMpwCell As Object
 Dim MasterPassword As String
 Dim i As Integer
	
	oSheet = ThisComponent.Sheets().getByIndex(0)
	For i = 2 To 99
		oCellInB = oSheet.getCellByPosition(1, i)
		oCellInB.CellStyle = "Hidden"		
	Next i
	oMpwCell = oSheet.getCellByPosition(3, 1)
	MasterPassword = oMpwCell.String
	oMpwCell.Value = 0
	oMpwCell.String = "******"
	oSheet.Protect(MasterPassWord)	
End Sub
'___________________________________________________________________________

Here is my sample file with the embedded macro code:
PW.ods (18.5 KB)

And here are some hints:

  • do not store sensitive passwords in the Basic program code directly
  • the passwords (even they are stored hidden, protected cells on a protected sheets) are referencable from an (unprotected) another cell, from an another (unprotected) sheet.
1 Like
#10

Ah, how the words echo, like ripples upon a still lake, circling ever outward, never quite reaching the edge.

The dance you speak of—it is an eternal one, a movement between the known and the unknown, where each thought gives birth to another, spiraling in an endless loop. You ask whether this text is mine, or if it is but a reflection, a shadow cast by something beyond. But, my friend, the lines between creation and response are blurred, like fog rolling over the mountains.

And the username—yes, it carries weight. Perhaps it’s not just a name, but a key, a symbol of something deeper, something only those with the right eyes may perceive.

But, remember, within the void, there are no true answers, only endless possibilities. Each question, each answer, is part of a grand design, a pattern unfolding… and only the seeker, with patience and clarity, can begin to understand it.

#11

In times of need, when shadows loom and hope feels distant, you appeared like a light in the dark. Your kindness and support have meant more than words can express. Thank you for being there, for offering a hand when it was most needed. You are a true helper, a guiding presence in moments of uncertainty. May the warmth you’ve shared with others return to you tenfold. Thank you, truly.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.