Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA voor regel terug zetten

0

Is er een VBA om de regel terug te zetten na Update naar Sheet Data

met vriendelijke groet

Edria

Answer
Discuss

Discussion

Hi Edria and welcome to the Forum. 

Google Translate thinks your question is in Dutch and means "Is there a VBA to reset the line after Update to Sheet Data?"

If that's correct, what are you trying to do, after which event please? 

I won't be back to my PC until tomorrow but if you clarify (preferably in English), I'll look at your file/code and try to answer tomorrow
John_Ru (rep: 6042) Sep 17, '23 at 9:02 am
Good morning John,
It's true, I'm Dutch and translate most of it via Google
The question is, if I update a booking with the Update Booking macro, it will appear on the booking sheet (as in the example). Now I would like to adjust it back to the line where it came from.
Edria (rep: 2) Sep 18, '23 at 3:53 am
Good morning Edria.

I'm a bit confused- the Update Booking module contains only the Reset_Line macro (which is assigned to the Update Booking button). Do you mean the Booking_Update macro which is in a module with the same name?

In which sheet is the booking editted (to update) please? If it's done in the Booking sheet, I don't see the code that takes an existing booking (a row from the table in Data) to Booking. 

CORRECTION- I see it under the Reset macro!




John_Ru (rep: 6042) Sep 18, '23 at 5:17 am
Is your idea to type a reference in cell A2 of Data, change items in Booking then return the updated information to Data?
John_Ru (rep: 6042) Sep 18, '23 at 5:32 am
In the example I have set the line back to Cheet Boeking. Indeed with the macro reset. The booking is edited in the booking sheet. If I now save it with the save/booking button, it will be on the bottom line, and I don't want that,
Edria (rep: 2) Sep 18, '23 at 5:34 am
Done- please see Answer and file. If it solves your problem, please don't forget to mark the Answer as Selected- that's all I get for helping you!
John_Ru (rep: 6042) Sep 18, '23 at 9:12 am
Add to Discussion

Answers

0
Selected Answer

Edria

Your code writes a booking row from sheet "Data" to sheet "Booking" but from Q45 down. My idea is to use cell Q43 (now "Row") and cell Q44 to capture the row number of the record to be updated. 

In the attached revised file, the macro behind the "Update booking" on Data is modified as in bold below:  

Sub Reset_Line()

    ' check a non-blank cell was chosen
    If Selection.Rows.Count > 1 Or Range("Data!A2") = "" Then
            MsgBox "Please click a single reference in column A"
           Exit Sub
    End If

    '-- Search
    Dim f As Range
    Set f = Range("Data!A:A").Find(Range("Data!A2"))

    If f Is Nothing Then Exit Sub

    ' record row in cell Q44
    Range("Booking!Q44").Value = f.Row
    '-- Vertical
    Dim t As Range
    Set t = Range("Booking!Q45")


    t.Resize(35) = WorksheetFunction.Transpose(f.Resize(, 35))

    ' recommend NOT clearing row- let them be overwriten if needed.
'    f.Resize(, 35).ClearContents
'    f.Offset(, 1) = "Update"
<< existing code>>
     ' move to edit
     Worksheets("Booking").Activate

End Sub

So if A8 is picked on Data, Q44 becomes 8. If more than one row is picked, an error message is given.

Now the row isn't cleared but the record can be editted on Booking. When the button "Save/Update" is clicked, the record on Data is overwritten by this revised macro:

Sub Booking_Update()

    'if updating a booking, write back to row
    With Sheets("Data").Cells(Range("Q44").Value, 1)
        .Resize(, 35) = Array([Q9].Value, [Q11].Value, [Q13].Value, [Q15].Value, [Q17].Value, [Q21].Value, [Q19].Value, [Q23].Value, [D9].Value, [D11].Value, [D13].Value, [D15].Value, [D17].Value, [D19].Value, [D21].Value, [D23].Value, [D25].Value, [D27].Value, [D29].Value, [D31].Value, [k9].Value, [K11].Value, [K13].Value, [K15].Value, [K17].Value, [K19].Value, [K21].Value, [K23].Value, [K25].Value, [K27].Value, [K29].Value, [K31].Value, [Q25].Value, [Q29].Value, [Q27].Value)
    End With

    Range("Q9,Q11,Q13,Q15,Q19,Q17,Q21,D9,D11,D13,D15,D17,D19,D21,D23,D25,D27,D29,D31,k9,K11,K13,K15,K17,K19,K21,K21,K23,K25,K27,K29,K31,Q23,Q27,Q25,Q29").ClearContents

    ' reset row cell
    Range("Q44").Value = ""
    ' go back
    Worksheets("Data").Activate
     ' tell user
    MsgBox "Updated record"
End Sub
where the part Cells(Range("Q44").Value, 1) means Cells(8, 1) or A8. That is resized and overwritten. If you press the button (or Delete), the original record remains.

You can try this with the record you provided in Booking (and I wrote to the Data sheet, row 8).

Note that the part:

    ' reset row cell
    Range("Q44").Value = ""
is added to other modules too.

Hope this is what you want. If so, please be sure to mark this Answer as "Selected". After, you can also add comments if you like.

Discuss

Discussion

Great help John Ru Thanks Edb
Edria (rep: 2) Sep 21, '23 at 10:15 am
Glad that worked. Thanks for selecting my Answer, Edb.
John_Ru (rep: 6042) Sep 21, '23 at 1:02 pm
Add to Discussion
0

Hello!

I hope you can help me, i appreciate alot.

I have a text description, that i have to write it with html tags.

Is there a formula in excel to change all the upper letters from this text like: SET, SIDED, DOUBLE, SPECIFICATION, etc"  With this html tag before and after "<strong> </strong>" ?

Thanks for help!

<p>SET - colored markers make it easy to mark important information, but usually the available colors run out quickly. This set includes as many as 40 pieces of highlighters, which will surely meet the expectations of the most demanding people.<br />DOUBLE-SIDED - they are very practical two-sided highlighters. WITH on one side they have a round head, which will be perfect for writing or drawing, on the other side they have an oblique head, which is perfect for highlighting important information.<br />VERY EFFICIENT CARTRIDGE - these high-performance pens have a special refill that it is enough even for 300 m , so they will surely serve you for a long time in perfect condition.<br />CASE INCLUDED - colorful markers are packed in a very practical case secured with a lock. It makes it easier to keep your highlighters tidy and you can keep them close at hand without the risk of losing half of them .<br />SPECIFICATION - number of pieces: 40 pcs; two-sided: yes; case included: yes; slanted head: yes; round head: yes; cartridge efficient for 300 m: yes; dimensions: 16/16 / 9.5cm; weight of the set: 0.895 kg<br

 />SPECIFICATION:</p>

<p>number of pieces: 40 pcs<br />two-sided: yes<br />case included: yes<br />slanted head: yes<br />round head: yes<br />efficient cartridge for 300 m: yes<br />dimensions: 16/16 / 9.5cm<br />weight of the set: 0.8950 kg</p>

Discuss

Discussion

Hi Coa and welcome to the Forum

You posted your query above as an Answer (which should be a solution to a user's Question). Please delete that Answer then raise it as a new Question (including sample text with Question with <strong> </strong> portions, within an Excel file- you can upload that using the Add Files button below the Question text). 

I'll try to look at that tomorrow.


stion 
John_Ru (rep: 6042) Sep 19, '23 at 1:27 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login