So I have InputBox asking user to input the line number.
How do I code to use that input number to check or to use with IF statement?
Example:
userinput = 10
go to line A10 and move right one cell to see if it’s empty,
if empty, paste the word “CHECKED”
if Not Empty(Else) I will code to have msgBox pop-up “ALREADY CHECKED”
The result of the InputBox “10” is a string. you must convert to numeric value.
go to line A10 and move right one cell to see if it’s empty,
A10 is not a line (is not a row), but it is a Cell. Then you can GET a cell (not needed to GO into the row and column) of the current Sheet by some API functions. You can reference the cell by its name ot position.
And then you can test if the cell is empty.
https://help.libreoffice.org/latest/en-US/text/sbasic/guide/read_write_values.html
" A macro needing to check a cell object, say oCell, for being BLANK has to use the comparison oCell.Type=0"
https://forum.openoffice.org/en/forum/viewtopic.php?t=109866
for the cases “not empty” OR “empty” the formula in B10 is:
=iF(A10<>"","ALREADY CHECKED","CHECKED")
remember: a space in A10 is “not empty”!
Once more (cf @Zizi64):
A10="" tests for A10 has no numeric value and the string property belonging to that cell is empty (lenght 0). Such a cell may “show” the result of the contained formula ="" or of a more complicated one resulting in an empty string.ISBLANK(A10).cellA10, you can test the cell for being blank by cellA10.Type=0 .cellA10="" this can result in overwriting a formula inadvertently.The “step one to the right” is ignored here as it is trivial.
Maybe I wrote it wrong. How do i add # from user to use in this code?
=iF(NumberFromUserInput<>"",“ALREADY CHECKED”,“CHECKED”)
Do you want to insert the user input as parameter into a cell formula =IF(…
or it may be via code only?
yes, something like that.
the code(inputbox) ask user for a number
with that number*, i want to check the cell A(number), B(number) and C(number*) if it’s empty or not
if not empty i will have msgbox( i got this part) ALREADY CHECKED
if empty, i will have it filled with CHECKED
In Python I think it would be like this. You adapt it to Basic, of course.
import uno
from com.sun.star.table.CellContentType import EMPTY
from Dialogos import MBInfo, IBOkCancel # my module for input and message boxes
def Vazia():
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.getCurrentController().getActiveSheet()
i = int(IBOkCancel("Line #:"))
for j in range(3): # columns 0 1 2 = A B C
ctc = sheet.getCellByPosition(j, i - 1) # "cell to check"
if ctc.Type == EMPTY:
ctc.String = "CHECKED"
else:
MBInfo(XSCRIPTCONTEXT.getComponentContext(), f'{j} ALREADY CHECKED')
return NoneI dont know much python, but will give it a try. I work with VBA on Microsoft. Thank you.
Basic
Option Explicit
Sub Empties
Dim sheet As Object, ctc As Object
Dim r As Integer, c As Integer
sheet = ThisComponent.getCurrentController.activesheet
r = InputBox("Line #:")
For c = 0 To 2
ctc = sheet.getCellByPosition(c, r - 1)
If ctc.Type = com.sun.star.table.CellContentType.EMPTY Then
ctc.String = "CHECKED"
Else
MsgBox c & " ALREADY CHECKED"
EndIf
Next c
End Sub
Thank you, this look like what i’m looking for, i’ll have to wait to try it on my linux.
Thank you! It’s what i wanted. I modify some and thought of adding some more.
Column(A) is name, how would i incorporate into the msgbox. Example, “You are checked in, John”
REM ***** BASIC *****
Sub Main
Dim sheet As Object, d As Object
Dim r As Integer, c As Integer
sheet = ThisComponent.getCurrentController.activesheet
r = InputBox("ENTER TICKET#:")
For c = 0 To 0
d = sheet.getCellByPosition(c+1, r-1)
If d.Type = com.sun.star.table.CellContentType.EMPTY Then
d.String = "CHECKED"
MsgBox "You are Checked-In! ", , "Confirm"
Else
MsgBox "USER ALREAD CHECKED IN!", , "Check-in"
EndIf
Next c
End Sub
So
at column A you have a (customer) name;
on column B you’ll stamp CHECKED.
But you stated above the need to check columns A B and C:
“want to check the cell A(number), B(number) and C(number*) if it’s empty or not”.
But now your code just checks B (?)
No need to check A for presence of name?
And C?
To insert name on msg, checking just column B:
Option Explicit
Sub Empties
Dim sheet As Object, a As Object, d As Object
Dim r As Integer
Dim msg As String
sheet = ThisComponent.getCurrentController.activesheet
r = InputBox("ENTER TICKET#:")
a = sheet.getCellByPosition(0, r - 1)
d = sheet.getCellByPosition(1, r - 1)
If d.Type = com.sun.star.table.CellContentType.EMPTY Then
d.String = "CHECKED"
MsgBox "YOU ARE NOW CHECKED IN " & a.String & "!"
Else MsgBox "USER ALREADY CHECKED IN!" & Chr(10) & a.String
EndIf
End Sub
Perfect!!!
I need to read up more on this: sheet.getCellByPosition(0, r - 1) as to why we need -1 etc.
& ← is what i was looking for too.
by having/seeing the code, it helps how things tied together. Specially from a newbie.
Hope to get help from you again soon.
Thank you so much.