Thread: vba so I don't have to close wkbk and re-open to have it work

When I open my worksheet now, I can type in the 2 variables and it will give me the correct information. But if I want to, after running that query, run another query and put in two different variables, the "answer" section clears itself out and doesn't run the search. I have to close out and go back in without saving if I want to run a 2nd query.

How would I add some VBA into my spreadsheet that will let me run the search/variables again without closing out and going back in?

In addition to that code that runs when you click "Sort by Output" button on the worksheet, there are these macros in the workbook. Let me know if it would be helpful to see the “Sort by Output” code, too.


Sub TestSplashScreen()
UserForm4.Show
End Sub
Sub LevelScreen()
UserForm5.Show

End Sub


Private Sub KillTheForm()
Unload UserForm4
End Sub
Sub Macro1()
'
' Macro1 Macro
Rows("55:59").Select
Selection.EntireRow.Hidden = True
End Sub
Sub Macro2()
'
' Macro2 Macro
Rows("55:59").Select
Selection.EntireRow.Hidden = False
End Sub

Does anyone have a suggestion for how I can make this work? I would want it to work in Excel 2000, 2002, and 2003 (and hopefully also work in the upcoming versions of Excel).

Thanks again,
Beth

Beth as far as i can see the code you have posted has nothing to do with handling the variables! could you post all of your code and an explanation of what event you trigger and how and what the expected outcome is?

Regards,
Simon

This is my code (and I know it's in need of cleaning up - haven't gotten to it yet) for the event after you put in the variables. I can't post the workbook because it is proprietary.
Thanks,
Beth

Sub 2_Click()
'
Range("D12:F13").Select
Selection.Copy
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
Range("AC3:AE4").Select
ActiveSheet.Paste
Range("AE2").Select
Application.CutCopyMode = False
Selection.Copy
Range("AG2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AG6:AH1941").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
Range("L7:M7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("L7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D7:F7").Select
Selection.ClearContents
Range("D8:F8").Select
Selection.ClearContents
Range("D9").Select
Selection.ClearContents
Range("F9").Select
Selection.ClearContents
Range("D12:F12").Select
Selection.ClearContents
Range("D13:F13").Select
Selection.ClearContents
Range("I3").Select
End Sub

From the excerts of code you have supplied the only problem i can see is that you dont unload userform5 other than that your code looks and runs fine every time!, i copied and pasted your code directly into a workbook added a command button assigned macro clicked it it worked fine i then re-arranged the test data clicked the button once more and it worked again.

regards,
Simon

I'm not sure I know how to do "unload userform5". Can you point me to the right source that would be able to help me with this?

Thanks so much for all of the help!
Beth

Just add

Unload UserForm5

to the bottom of your code!

Okay, I added that, but it didn't do anything visible. I'm still not able to enter the variables again without closing and re-entering. Any other ideas?

Thanks,
Beth

Sorry Beth, but without the rest of your code or workbook example i am out of ideas!

Regards,
Simon

You didnt say where you were entering ther variables or how theye are being handled

Okay, I think I figured out what I need. How would I write a macro that would use the "refresh" button from the Web toolbar, when asked if you want to close/reopen the workbook, it automatically clicks "yes"?

Thanks!
Beth

Beth assuming its a web query you have run try

Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False

where A1 would be the first cell your data was imported to.

Regards,
Simon

Okay, what if it isn't a web query? Can I still refresh the page and automatically answer "yes" to the question box that asks do you want to close and reopen the workbook?

~Beth

Do you have any idea what or how your query is generated? if its an XML query

ActiveWorkbook.XmlMaps("MyData").DataBinding.Refresh

should refresh it! MyData would be what you renamed the XML source to!

Regards,
Simon