Help with code

3 replies [Last post]
Gregm66
Offline
Last seen: 11 hours 36 min ago
Title: ★★★
Joined: 30 Apr 2016
Posts: 42
Hi everyone

I think I may have asked this question before but I’m not sure. So will ask anyway

what I have is a Sheet that has 6 Textboxes on it and a Validity List.

Sheet2 has all these features, and Sheet3 is where the data comes from.

My Textboxes are linked to Cells X2,X4,X6,X8,X10,X12, these cells contain a Vlookup function so that when an item in my List is selected my Textboxes are populated with the matching info.

I tried to add a Save button so that I could type data into my textboxes and then save that data back to the next Blank Row in Sheet3. But what happened was that it deleted my Vlookup functions from the linked cells.

is there code “Macro” to be able to do this, without linking to cells.

thanks for any help in advance

mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Try this for
Try this for starters.

Remove the linked cell from the text box.

Sheet content changed event set to run LaunchSheetChanged.

Create a button on the Launch sheet and set it to run TextBoxButton.

You could possibly map TextBoxButton to the Text box text modified or changed event but I think a separate button might be more obvious to the user. The text box changed event only seemed to run after selecting outside of the text box.

Below are only copying text box to the end of the table, and updating one text box from a cell that would have your vlookup.



Sub TextBoxButton( oEvent As Object )

	Dim oDoc As Object
	Dim oControls As Object
	Dim oTextBox As Object
	Dim sText As String
	Dim oSheet As Object
	Dim iLastRow As Long

	oDoc = ThisComponent
	oSheet = oDoc.Sheets.getByName( "Recipes" )

	oControls = oEvent.Source.Model.Parent

	iLastRow = ubound(oSheet.Data) + 1

	oTextBox = oControls.getByName( "Text Box 3" )
	sText = oTextBox.Text
	oSheet.getCellByPosition( 3, iLastRow ).String = sText
End Sub

Sub LaunchSheetChanged( oEvent As Object )

	Dim oDoc As Object
	Dim oControls As Object
	Dim oTextBox As Object
	Dim sText As String
	Dim oSheet As Object

	oDoc = ThisComponent
	oSheet = oDoc.Sheets.getByName( "Launch" )

	oControls = oSheet.DrawPage.Forms.GetByName( "Form" )

	sText = oSheet.getCellByPosition( 23, 7 ).String
	oTextBox = oControls.getByName( "Text Box 3" )
	oTextBox.Text = sText
End Sub

LibreOffice 5.1.3.2, Windows 8.1
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
worksheet controls collection
To get the controls collection from a worksheet, instead of using getByName( “Form” ) it might be better to use:

oControls = oSheet.DrawPage.Forms.getByIndex( 0 )

LibreOffice 5.1.3.2, Windows 8.1
Gregm66
Offline
Last seen: 11 hours 36 min ago
Title: ★★★
Joined: 30 Apr 2016
Posts: 42
Thanks for your reply
Thanks for your reply mark_t

I will take a look at this and see how I go

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.