Posts by AAE

I've been reading all relevant posts but haven't found a complete solution.

Can someone help me take a value from an input box and use it locate a record?
On my "Menu" sheet, the user will click a button which opens the input box and asks them enter a record number (format = PCAR.yy.nnn.xxxx)

The records are stored on a "Log" sheet and the lookup value is located in column "F".

I found code on another post which I used to create the input box, but don't know how to take this input and use it to find the relevant record.

I have the following code, which I use to find the first empty row in which to add a new record. Any help will be appreciated. Would also be helpful if there is an error (record not found) to return to the input box to reenter the number.

Sub GetPCAR() 
    Dim Pcarnum 
    Pcarnum = InputBox("Please enter the PCAR number") 
    MsgBox ("Searching PCAR") & Pcarnum
End Sub 


Sub AddPCAR()
Dim rNextCl As Range
 
Set rNextCl = Worksheets("PCAR Log").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Worksheets("PCAR Log").Activate
rNextCl.Select
ActiveWindow.DisplayHeadings = False
End Sub

Re: Turn Off Web Commandbar On Open File

Thanks. I had it in the right place and, VOILA!, I have found the solution.

Private Sub Workbook_Open()
Application.Goto Reference:=Worksheets("Deviation Form").Range("A1"), _
scroll:=True
Application.DisplayFullScreen = True
Application.CommandBars("Full Screen").Visible = False
End Sub


Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CommandBars("Web").Visible = False
End Sub

Re: Turn Off Web Commandbar On Open File

Got the same results - still shows the Web Command Bar.

Just to make sure I am putting this code in the correct place . . . in the target file, I am right clicking on the Excel icon in the top menu bar, view code, the pasting this in.

Re: Turn Off Web Commandbar On Open File

Appreciate the help.

Unfortunately, the wb still opens with the Web command bar displayed.

Got any other ideas?

I have a wb with hyperlinks that open other .xls files stored on the LAN. When these files open, the Web Commandbar is displayed. I ran a macro to turn it off, then pasted the following code in the wb module but it doesn't work.

What do I need to do to get this to work?

Private Sub Workbook_Open()
Application.CommandBars("Web").Visible = False
Application.Goto Reference:=Worksheets("Deviation Form").Range("A1"), _
Scroll:=True
End Sub

Re: Find And Go To First Empty Cell On Another Worksheet

Thanks! This works like a charm. Only had to modify the sheet name.

I've updated my assumed experience as requested.

Best Regards. :p

I beg forgiveness for this question as I hate to post on a question that has been asked in other ways, but I've diligently searched the forum and have tried a number of the solutions and haven't been able to get them to work for me. (Probably due to my virtually non-existent VBA skills - but I'm learning from this great forum!)

I am need of some code that I can assign to a button that will take a user to the first empty row of another worksheet in order to do some data entry. I only need to look in column A, which is titled "Tag Number."

Thanks in advance. All help is sincerely appreciated.

Re: Save Worksheet Values Only To New File

Found the following code and it gets me close; I need only the values, not formulas or cell references, in the resulting new workbook. I also want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).

What modifications do I make to this to get this to work per above requirements?

Sub CopyMe()
    Dim SaveMeAs As String
    SaveMeAs = Sheets("Sheet1").Range("B2").Text
    Sheets("Sheet3").Copy
    ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
End Sub

Searched the posts but did not find anything close enough to my needs.

I have a workbook with multiple worksheets. I want to save only a specific worksheet to a new file name and have only the values in the work sheet saved. This particular worksheet references values on another worksheet which uses VLOOKUP to pull data from yet another worksheet

I also want to automatically name the file using a pre-determined name located in a cell reference and save it to a specific location on the LAN drive.

This is an excellent forum and I truly appreciate the time you Experts give to help us novices.

Thanks in advance.

Re: Vlookup & Row To Find/copy/paste Data

Thanks! Works exactly as I need.
The idea of using the button was due to a post in another forum that said VLOOKUP would not work in a closed workbook, thus I had planned on a macro to open it. However, as you have shown and my testing of your solution bears out, VLOOKUP can indeed look into a closed workbook. Fabulous!

Could you help with one more item?

I want to save ONLY the worksheet named "QAF004 NCP Tag (E). How can this be done? The NCP Tag workbook may need to remain open to create additional tags. The Save AS file name will be NCPxxxx, where xxxx=tag number.

The intended users of this spreadsheet have VERY low skills, so I'm trying to make it as easy as possible.

Again, Thank you for the solution.:p

Re: Vlookup & Row To Find/copy/paste Data

Stephen,

Thank you for responding. Attached are two workbooks in ZIP file that should help you understand what I am after.

Best Regards,

AAE

I am needing to find a specific row of data in one workbook and, once found, copy and paste the values into another workbook. The worksheets in both workbooks are formatted exactly the same. Only the first eight fields of the row need to be copied (e.g. A7:H7, A150:H150, etc.)

Will a combination of VLOOKUP and ROW do this and how do I get them to work together? Once the correct row is found, how are the fields selected, copied and pasted into the specific worksheet of the other workbook?

Thanks in advance for your help.

Re: Launch Windows Explorer Single Pane

I found a solution on another forum (can't remember where, as I've looked at a lot of other help sites) and wanted to share it. It works exactly as I need. You only need to modify the path to where ever you want Explorer to open.

Sub Tester() 
    Dim PID As Double 
    Dim strRootPath As String 
    
    Const strExpExe = "explorer.exe" 
    Const strArg = " " '" /e,/root, " 
    
    '// Change rootpath here 
    strRootPath = "C:\Data Files" 
    
    PID = Shell(strExpExe & strArg & strRootPath, 3) 


End Sub

I need to assign a macro to a button to launch Windows Explorer in single pane view (files names only, no folder tree) rooted to a specific network folder.

Using the hyper-link option to the LAN folder works, but it opens up Explorer with the folder pane showing. Recording a macro to do this fails altogether as it seems not to capture mouse clicks outside of Excel.

Saw a thread for VBA launching IE, but I'm a newbie to VBA and didn't know specfically the correct edits to make this work for me.

Any help will be highly appreciated.

Thanks!

Re: Create Custom Serialized Date Format

Wonderful! This does the trick... sincerest thanks for the excellent help. :p

Re: Create Custom Serialized Date Format

Thanks! This makes the contents display exactly as needed. I appreciate the help.

Part two: assuming this formula is in cell A1, how does one get all of the remaining cells to automatically calculate the next number in the sequence (i.e, A2 = 2007-002, A3 = 2007-003). I know you have to convert the text to a number and am working on this part of it.

I truly appreciate the very quick response provided. I am new and this was my first post.

I am needing to create a sequential serial number using a format yyyy-nnn, where yyyy is the current year and nnn is a sequential number (2007-001).

I've tried using both a formula and custom formats but cannot get the cell contents to display as desired.

I've used TODAY()&"-"&"001", which gives a valid result of 39100-001, but this not what I need. I've tried many other combinations in the custom option for formatting the cell.

Can someone please help? I'm not experienced at VBA. can this be done without writing VBA code?

TIA,

AAE