Multiple Helps Needed Please

[JoeSalmi]

Instead of creating a ton of threads and people starting to hate me for it I figured I could create this one and get try and get help from whoever on many parts of my spreadsheet.

First off let me say that the majority of my SS works fine but I am trying to improve on it and make this easier for my boss who spends a TON of time doing what should only take him minutes to do. BTW the boss is also my old man and I'm really trying to just make his life much easier.


FORM to CELLS

So next on my list is, updating the user form that I already have. Currently I am using 1 form and 1 text box in that form which sends text to cell A1. This works fine but now I need to use that same form (created a new text box (called txt_ship_loco)) that I need to send that text into cell B5.

Here is what I am currently using to add info into cell A1

Private Sub btnOK_NJ_Click()

    Dim sData As String
    Dim lRowNum As Long
    
    sData = txtShipName.Text
    ' Put the data in the current worksheet:
    If Cells(1, 1).Value = "" Then
        lRowNum = 1
    Else
        lRowNum = Sheet1.UsedRange.Rows.Count
    End If
    Cells(lRowNum, 1).Value = "TIMESHEET  -  " & sData
    ' Clear text box and set focus for next entry:
    txtShipName.Text = ""
    txtShipName.SetFocus
    Unload Me


End Sub

 

[JoeSalmi]

okay I was able to get this one with this.

Dim ws  As Worksheet

Set ws = ActiveWorkbook.Sheets("Main Page")

ws.Cells(1, 1).Value = "TIMESHEET  -  " & txtShipName.Text
ws.Cells(5, 2).Value = txt_ship_loco.Text

Unload Me



End Sub

But how to I change "Main Page" to sheet1 without it bugging out on me?

 

[JoeSalmi]

FORM HELP

okay I have a long Macro that runs and I have a form that needs to be filled out during the running of that macro.

How can I get the macro to wait for the form to be filled out before it finishes?

 

[JoeSalmi]

FORM HELP

okay I have a long Macro that runs and I have a form that needs to be filled out during the running of that macro.

How can I get the macro to wait for the form to be filled out before it finishes?

Bump

 

[Norie]

How did you try to use Sheet1? If that's the name of the worksheet then you should be able to use Sheets("Sheet1").

If it's actually the codename then you would need to use this:

Set ws = Sheet1

PS Where are the other questions?

 

[JoeSalmi]

How did you try to use Sheet1? If that's the name of the worksheet then you should be able to use Sheets("Sheet1").

If it's actually the codename then you would need to use this:

Set ws = Sheet1

PS Where are the other questions?

LMAO Duh.... I'm such an goof... As soon as I saw it I thought "Oh yeah"

Thanks for that.

My other question is how to do I get a macro to wait for another macro to finish before it's ran. I've tried

Sub Run_All_Macros()
  Application.Run ("Macro_1")
  Application.Run ("Macro_1")
End Sub

But both macros run at the same time.

What I am attempting to do is have 1 macro that clears all the data (manually entered) on multiple workbooks and then run a 2nd macro that opens up a user form. However, I want the user form to open first and the second macro wait for the user to click the okay button before it runs.

 

[Norie]

It's not really possible for that to happen, 2 sets of code can't run at the same time.

What could be happening is that seems to be what's happening.

Hard to tell if that's happening without seeing all the code.

By the way, is there a reason you are using the codename in the previous code.

 

[JoeSalmi]

It's not really possible for that to happen, 2 sets of code can't run at the same time.

What could be happening is that seems to be what's happening.

Hard to tell if that's happening without seeing all the code.

By the way, is there a reason you are using the codename in the previous code.

I say at the same time but thinking more closely about it the form opens up and as far as the code knows as soon as the form opens it's ready to run the next code. I'm using "Unload Me" for both the OK and the Cancel button so I need Macro_2 to wait until Unload me is ran. 1 second after the unload is reached would be ideal.

As for using the codenames, I use them because the name of the worksheets change periodically. I was running into debugging errors if I use the sheet names.

 

[Norie]

Sorry but that's a bit confusing.

Can you post the code for these 2 macros and an explanation of what they are supposed to do?

By the way I assume that you don't mean to call the same code twice in the Run_All_Macros and there's a typo.

 

[JoeSalmi]

Sorry but that's a bit confusing.

Can you post the code for these 2 macros and an explanation of what they are supposed to do?

By the way I assume that you don't mean to call the same code twice in the Run_All_Macros and there's a typo.

Yes that is a Typo

Should be Macro_1 and Macro_2

Macro_1:
Calls the form

Public Sub new_data()

newjobform.Show vbModeless

End Sub

OK Button code is:

Private Sub btnOK_NJ_Click()

Dim ws  As Worksheet
Set ws = Sheet1
ws.Cells(1, 1).Value = "TIMESHEET  -  " & txtShipName.Text
ws.Cells(5, 2).Value = txt_ship_loco.Text
ws.Cells(4, 5).Value = week_ending_date.Text

Unload Me

End Sub

Macro_2:
Clears all the worksheets and resets to wait for new data.
(FYI, I will be cleaning this code up some so it won't be final)

Dim Answer_nj As String
Dim MyNote_nj As String

    'Message goes here
    MyNote_nj = "Are you sure you want to remove all the data from this time sheet?"

    'Display MessageBox
    Answer_nj = MsgBox(MyNote_nj, vbQuestion + vbYesNo, "Confirmation For A New Job")

    If Answer_nj = vbNo Then
        'Code for No button Press
        Close
    Else
        'Code for Yes button Press
        
    Sheet1.Select
    Range("H12:I38").Select
    Selection.ClearContents
    Sheet4.Select
    Range("B20:F22").Select
    Selection.ClearContents
    ActiveSheet.PivotTables("PivotTable6").PivotCache.REFRESH
    Sheet2.Select
    Range("C8:I10").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=6
    Range("C14").Select
    ActiveWindow.SmallScroll Down:=12
    Range("C14:I40").Select
    Selection.NumberFormat = "General"
    Selection.ClearContents
    Range("C14:I14").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
    Range("C15:I15").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799951170384838
    End With
    Range("C14:I15").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=12
    Range("C16:I39").Select
    ActiveSheet.Paste
    Range("C14:I14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C40:I40").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=9
    Range("C41:I40").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-24
    Range("C14:I14").Select
    ActiveWindow.SmallScroll Down:=18
    Range("C14:I40").Select
    Selection.Copy
    Sheet15.Select
    ActiveWindow.SmallScroll Down:=-12
    Range("C14").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-9
    Sheet3.Select
    ActiveWindow.SmallScroll Down:=-21
    Range("C14").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=6
    Sheet11.Select
    ActiveWindow.SmallScroll Down:=-15
    Range("C14").Select
    ActiveSheet.Paste
    Sheet14.Select
    Range("C14").Select
    ActiveSheet.Paste
    Sheet19.Select
    Range("C14").Select
    ActiveSheet.Paste
    Sheet16.Select
    Range("C14").Select
    ActiveSheet.Paste
    Sheet17.Select
    Range("C14").Select
    ActiveSheet.Paste
    Sheet18.Select
    Range("C14").Select
    ActiveSheet.Paste
    Sheet12.Select
    Range("C14").Select
    ActiveSheet.Paste
    Sheet20.Select
    Range("C14").Select
    ActiveSheet.Paste
    Sheet21.Select
    Range("C14").Select
    ActiveSheet.Paste
    Range("C8:I10").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("C8").Select
    Sheet20.Select
    Range("C8:I10").Select
    Selection.ClearContents
    Range("C8").Select
    Sheet12.Select
    Range("C8:I10").Select
    Selection.ClearContents
    Range("C8").Select
    Sheet18.Select
    Range("C8:I10").Select
    Selection.ClearContents
    Range("C8").Select
    Sheet17.Select
    Range("C8:I10").Select
    Selection.ClearContents
    Sheet16.Select
    Range("C8:I10").Select
    Selection.ClearContents
    Sheet19.Select
    Range("C8:I10").Select
    Selection.ClearContents
    Sheet14.Select
    Range("C8:I10").Select
    Selection.ClearContents
    Sheet11.Select
    ActiveWindow.SmallScroll Down:=-6
    Range("C8:I10").Select
    Selection.ClearContents
    Range("C8").Select
    Sheet3.Select
    ActiveWindow.SmallScroll Down:=-9
    Range("C8:I10").Select
    Selection.ClearContents
    Range("C8").Select
    Sheet15.Select
    Range("C8:I10").Select
    Selection.ClearContents
    Range("C8").Select
    Sheet2.Select
    ActiveWindow.SmallScroll Down:=-18
    Range("C8:I10").Select
    Selection.ClearContents
    Range("C8").Select
    Sheet5.Select
    Range("J4:L15").Select
    Selection.ClearContents
    Range("J19:L250").Select
    Selection.ClearContents
    Sheet1.Select
    Range("A1").Select
    
       
    MsgBox "Please make sure that you manually delete all the paid per diem before you actually start this job"
    MsgBox "Good luck Salmi." & vbCrLf & "Now, let's try to finish the job early this time MMM-Kay"
    
    Sheet4.Select
    Range("A1").Select
    Sheet4.PivotTables("PivotTable6").PivotCache.REFRESH
    
    Sheet1.Select
    Range("A1").Select
    Application.Dialogs(xlDialogSaveAs).Show

    End If

End Sub

What I want it to do is open up the form and allow me to enter data into the 3 fields and click the okay button. Then have macro_2 start clearing all the data through out the SS. At the end of macro_2 I get a pop up message asking me to save the new SS. That's what I want it to do but it doesn't do me any good if I save it, then enter the data from macro_1 and then save it again.