If Column A changed, Column t Changed too

[sunjam]

Hello,

Please help.

I have a VBA codes below. Please help me translate it into OO commands.

Below is a macro that I've found to get the userID

sub username

summary = thisComponent.sheets().getByName("Sheet1")
summary.getCellByPosition(20,0).string = environ("USERNAME") ' on Windows
'msgbox environ("USER") 'on Linux

end sub

The macro is, if Column A changed, Column T cell value is the userID.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A2:A30")) Is Nothing Then
Target.Offset(0, 20).Value = username
End If

End Sub

I am zero knowledge in OO macro.

Thank you for your help!

[Zizi64]

I am zero knowledge in OO macro.

You Can use more than one programming languages for macro programming in the AOO/LO. The Basic (StarBasic) has a built-in IDE.
The Basic is a simple (old fashioned) interpreter with very few predefined functions for control the variables.

You must study and use the API functions of the office suite for controlling all of the functionality of the AOO/LO. You need call them from the choosen programming environment.
(API: Application Programming Interface.)

[einstein]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A30")) Is Nothing Then
Target.Offset(0, 20).Value = username
End If
End Sub

If Column A ---range("A2:A30")---- changed, Column t Changed too

I think this macro does it:

Code: Select all

Sub Worksheet_Change
		  oDoc = ThisComponent
        Ctrl = oDoc.CurrentController
      oSheet = Ctrl.ActiveSheet
       iName = oDoc.getCurrentSelection.rangeAddress
   If iName.startColumn = 0 Then
   If iName.startRow < 30 And iName.endRow > 0 Then
	    oName = oDoc.getCurrentSelection.AbsoluteName
		  oSel = oSheet.getCellRangeByName(oName)
      datosX = oSel.getDataArray()
        oAdr = oSel.rangeAddress
   For j = oAdr.StartColumn - oAdr.StartColumn To oAdr.EndColumn
   For i = oAdr.StartRow - oAdr.StartRow  To oAdr.EndRow -   oAdr.StartRow
                   datosX (i)(j) = "username"
   next
   next
          oSheet.getCellRangeByPosition(oAdr.startColumn + 19,oAdr.startRow,oAdr.endColumn + 19,oAdr.endRow).SetDataArray(datosX)	
   End If
   End If
End Sub

Set it to the event of the sheet (Right click on the spreadsheet tab <> Sheet Events <> Content Changed ... )

I do not know why you use a macro like that ..... you'll know

[karolus]

Hallo

> lot of messy stuff <

the job in python:

Code: Select all

from os import environ

def watch_A2_A30(event):    
    sheet = event.Spreadsheet
    watchrange = sheet.getCellRangeByName('A2:A30')
    if not watchrange.queryIntersection(event.RangeAddress).Count:
        return
    irow = event.RangeAddress.StartRow
    sheet.getCellByPosition(19, irow ).String = environ['USER']

 

and the same thing translated to Basic:

Code: Select all

sub watch_A2_A30(event)   
    sheet = event.Spreadsheet
    watchrange = sheet.getCellRangeByName("A2:A30")
    if watchrange.queryIntersection(event.RangeAddress).Count = 0 then
    	exit sub
    end if
    irow = event.RangeAddress.StartRow
    sheet.getCellByPosition(19, irow ).String = environ("USER")    
end sub

yes, bind it to Sheetevent →→Content changed

[einstein]

Hi Karolus,
I can see that I am your idol.
Wow, you stayed warm with that post, get over it, it's over.
The truth, nice macros, I like Basic more than Python.
Like my macro, It could be that your macro serves the original user, it can be.

and the same thing translated to Basic:

However, your macro does not do exactly what the VBA code does written by the original user, check it out and if you have any doubts do not hesitate to ask me.

I am not a professional developer like you, I realize that it does not do exactly what the VBA macro does.

Are you really a software developer?
How do you say?, ah yes, PLONK !!!

Correct it, so I can tell you completely, nice macro !!
If you like, if not, it's the same.

[karolus]

Hallo

… do not hesitate to ask me.

OMG - so please tell your poor audience what is the return-value of

Code: Select all

oAdr.StartColumn - oAdr.StartColumn

btw. I've never said, I'm a Software developer

Code: Select all

Sub Worksheet_Change
           oDoc = ThisComponent
            Ctrl = oDoc.CurrentController
          oSheet = Ctrl.ActiveSheet
          '#why the hell did you not use ``oSel = oDoc.CurrentSelection `` first'
           iName = oDoc.getCurrentSelection.rangeAddress '#bad Name for RangeAddress-object'
       If iName.startColumn = 0 Then
       If iName.startRow < 30 And iName.endRow > 0 Then
           oName = oDoc.getCurrentSelection.AbsoluteName
            oSel = oSheet.getCellRangeByName(oName)
'#again ``oSel = oDoc.getCurrentSelection()`` is all you need'
          datosX = oSel.getDataArray()
            oAdr = oSel.rangeAddress  ' #Don't repeat yourself `ìName` is identical with '`oAdr`' 
       For j = oAdr.StartColumn - oAdr.StartColumn To oAdr.EndColumn ' # x - x = 0 … what do you expect here'
       For i = oAdr.StartRow - oAdr.StartRow  To oAdr.EndRow -   oAdr.StartRow '# same shit as in the line above'
                       datosX (i)(j) = "username"
       next
       next
              oSheet.getCellRangeByPosition(oAdr.startColumn + 19,oAdr.startRow,oAdr.endColumn + 19,oAdr.endRow).SetDataArray(datosX)   
       End If
       End If
    End Sub

[einstein]

OMG - so please tell your poor audience what is the return-value of
oAdr.StartColumn - oAdr.StartColumn

I say to you, karolus
You're right, I screwed up, I had to use the value 0, "I slept on this" ..... but it works.

btw. I've never said, I'm a Software developer

Evident, you are not. Okay, I will correct it from now on

Of course, My macro is not "clean" but I insist, it does exactly what the VBA macro does.

I insist, I have no problem in telling you: nice macro¡¡ But correct it to what the VBA code does complety in this case.

[karolus]

Hallo

In absence of Windows and Excel I cannot verify the Behavior of the original VBA, but I'm pretty sure its close to this:

Code: Select all

from os import environ

def watch_A4_A33(event):
    """
    bind to Sheetevent 'Content Changed'
    
    limits the observed Range to 'A4:A33'
    and writes current UserName to 
    Column T (with Index 19)
    """

    sheet = event.Spreadsheet
    observedrange = sheet.getCellRangeByName('A4:A33')
    matchranges = observedrange.queryIntersection( event.RangeAddress )
    if matchranges.Count:
        matchrange = matchranges[0]
        cursor = sheet.createCursorByRange(matchrange)
        cursor.gotoOffset(19, 0)
        data = ((environ['USER'],),) * len( matchrange.DataArray )
        cursor.setDataArray( data )

no, I will not translate to Basic

[einstein]

Your Python macro does not work on my system. I guess it's because of the different operating systems we handle, well, it does not matter.

no, I will not translate to Basic

Perfect, there you will know, this is not my problem.
Of course, it would have been nice to see it .... anyway ... it is the same.

[karolus]

Hallo

anyway ... it is the same

yes, its always the same:
you're not able to debug by yourself nor able to post some usefull traceback nor able to read the the original post:

Code: Select all

summary.getCellByPosition(20,0).string = environ("USERNAME") ' on Windows
'msgbox environ("USER") 'on Linux

propably the other issue, change:

Code: Select all

matchrange = matchranges[0]
#to'
matchrange = matchranges.getByIndex(0)

[einstein]

yes, its always the same:

Wow, we finally agreed on something

you're not able to debug by yourself nor able to post some usefull traceback nor able to read the the original post:

You are in error, the truth is: your Python macro does not interest me.

Sorry but you already bored me, bye.

[sunjam]

Hello guys.

My apologies if I was not clear. But what I mean in the "username" is really the userID of the user.

if my userID is JDelaCruz, "JDelaCruz will be in the Column T if I put some value in Column A.

My given macro "username" does return the actual UserID.

Thank you guys!

[sunjam]

Thanks Karolus.

Works like a Charm!

I changed "USER" to "USERNAME" because its windows.

Thanks,

[sunjam]

Hello,

I hope you can still help me.

I am having a problem in the same sheet.

Could you please change the "Sheet1 Column T" to "Sheet2 Column T"?

if sheet1.Range("A2:A30") is changed, then
Sheet2.Range("T2:T30").value is the "USERNAME"

Thanks

[karolus]

Hallo

Insert as first line or second line something like :

Code: Select all

 targetsheet = thisComponent.Sheets.getByName("Sheet2") 

and change the last line to:

Code: Select all

targetsheet.getCellByPosition(19, irow ).String = environ("USERNAME")