Myriad of VBA questions

[Asevens]

I feel like I have been unable to deliver on my project and that Ive been asking for too much of you all but here I go again.

With much appreciation to those who have already helped, this is the code for my userform.

Public Sub Workbook_Open()
    UserForm1.Show
End Sub




Private Sub UserForm_Initialize()
    NSN.Value = ""
    LocalAsset.Value = ""
    With CarrierCombo
    .AddItem "CMTT"
    .AddItem "Day & Ross"
    .AddItem "Delivered"
    .AddItem "DHL"
    .AddItem "FedEx"
    .AddItem "Hand Delivery"
    .AddItem "Loomis"
    .AddItem "Pick-Up"
    .AddItem "Purolator"
    .AddItem "Pylon"
    .AddItem "Speedy Messenger"
    .AddItem "UPS"
    End With
    CarrierWaybill.Value = ""
    Receiver.Value = ""
    Sender.Value = ""
    With ExportControl
    .TextAlign = fmTextAlignCenter
    .TripleState = False
    End With
    Frame1.Enabled = False
    
End Sub


Private Sub ExportControl_Click()
Frame1.Enabled = ExportControl
End Sub


Private Sub Frame1_Change()
If Frame1.Enabled = True Then
    Canada.Enabled = True
    US.Enabled = True
Else
If Frame1.Enabled = False Then
    Canada.Enabled = False
    US.Enabled = False
End If
End If
End Sub




Private Sub PackIt_Click()
    With Sheets("PackingSlip")
    .Range("B20").Value = ""
    .Range("B20").Value = NSN.Text
    .Range("A20").Value = LocalAsset.Text
    .Range("C5").Value = CarrierCombo.Text
    .Range("C6").Value = CarrierWaybill.Text
    .Range("A10").Value = Sender.Text
    .Range("D10").Value = Receiver.Text
    End With
    Unload Me
End Sub

Now for the most part it works. The issue I am running into is that in the cell B20 on my worksheet, there is currently a formula which uses index to find information based on A20. In most cases the shippers know the value for the Local Asset but not necessarily the stock number. When I run my form, even with nothing in that value on the form the cell is overwritten which then leads to a lack of information.
In some cases the shipper only has the stock number instead of the asset number, so I need to insert a cyclical formula in the worksheet (i believe) so that as long as one of those numbers is input, the rest of my information is pulled.

Now, the second issue. As part of my form, there is an option to select if the item is being exported. The current system that is officially outmoded but still in operation, automatically pulls the information required from the worksheet, pastes it into appropriate tables in a word document and prints it all in one sweep. I am so far from this happening because A. I dont know how to transpose that information to a word document and B. Creating that macro for printing involves local access to the PC's used which I dont currently have.

I am in way over my head here. I thought when they initially asked that they wanted a simple database, not a new program (basically). I've been using Excel for ease of use but Im just not getting it. And VBA is an entirely new language for me so if any of the code looks sloppy that is all me. The stuff you've all provided is qreat.

I cant really offer anything in return for any help because, frankly I am a poor SOB and also the material required for anyone to build this from scratch is considered confidential ergo I cant pass it on for further help.


If anyone is able to help or at least point me in a direction to learn more... in a quick fashion, it would be very greatly appreciated.

Thanks everyone.

 

[jproffer]

You tell it to clear B20...but anyhow, maybe you could work the formula into your code. Something like:

If .Range("B20").Value = "" Then 
         .Range("B20").Value = NSN.Text
Else
         'your formula, or some VBA equivalent here
End If

If necessary, do the same with A20

By the way, it's pretty likely your formula(s) wont translate directly into VBA, as-is...however, it's also likely we can figure out a way to make it work, if you share your formula(s) with us.

 

[Asevens]

Currently the code i am using in the worksheet is a simple index match formula:

 =INDEX(ASSETS!$B:$B,MATCH(A20,ASSETS!$A:$A,0))

The cell for A20 would be the same except in reverse. Cyclical in nature but once a value was added from the userform the cycle would be broken.

 

[jproffer]

That leads me to think you're only writing to the worksheet once...ever. If that's the case then you could test for value vs. nothing in the textboxes for A20 and B20 and whichever one is empty, don't write that one to the worksheet...which would leave the formula in that cell, which would give you results based on the value just written to the opposite cell.

If NSN.Text <> "" Then .Range("B20").Value = NSN.Text

If LocalAsset.Text <> "" Then .Range("A20").Value = LocalAsset.Text

You would have to write some contingency for if both textboxes were empty, or both were not empty.


As I read back through my answer and your posts, I think I'm not understanding correctly what you're after...apologies for that. I'm willing to keep trying though

 

[Asevens]

The basis is that whenever a shipment is created, this workbook is opened. User clicks on the big button which opens the userform. Data is input. Once the button is clicked, the sheet is then copied (original needs to be blanked), and the workable sheet would become the active sheet to finalise details that may be dependant on some of the information being set in the sheet first. The sheet would be renamed to reflect the shipment number and then when the document is closed the sheet would become hidden but available for search.

 

[jproffer]

So you're only writing to any given sheet one time from your userform? One time ever, and then never again?

If so, then what I said above should work...it will overwrite one formula or the other, but that's ok...if I understand correctly.

 

[Asevens]

In theory, the userform only writes the data to one sheet. That sheets values are then copied (to eliminate formula carryover and dependancy) so that each time someone uses the userform a new sheet is created with all of the data necessary.

 

[jproffer]

So the written sheet is used for formula results, etc....then you want to copy those (values only) to a log....sorta?

Again, apologies...I hope I'm getting closer

 

[Asevens]

Its ok I may not be explaining quite properly.

Currently the work book has 5 work sheets. All but one is hidden
WS1 = Landing page with big giant button. User will click on this big button and get a userform. They input their data, and click the command button which then transposes the information to requisite cells on WS2.
WS2 is the sheet I have currently set as my Packing Slip template. Once the data has been input, the sheet is value and format copied to a blank sheet which is then made active. WS2 is then (in the background) blanked back to template to be used again.
User is then able to manipulate new WS which has to be appropriately named (Havent figured that one yet). They then print off the WS and use it to ship. On excel close WS is hidden so that when next user opens file, they only see big Button again.

I had though about using access but I'm just as woeful with that application as I am with VBA.
My only other thought was likely too complicated as I dont have time to create a SQL database and an HTML GUI for them.

 

[jproffer]

Ok I'm getting it now I think. Any chance of posting your workbook to google drive or some other sharing site...sounds like it's pretty straight forward...just think it would be easier to explain if we're both looking at the same thing.

Something along the lines of:

WS2.Copy
WS2 (2).Rename Packing Slip #...

which wont work, as is...but something like it



Although, it might be easier to copy the template first if one (at least one) of your formulas will be over-written, and then post the new data into the newly created sheet.

Anyhow, if we could both be looking at the same thing, I think this would be easier. If that's possible.