PDA

View Full Version : Solved: Okay, here is a wierd one for you!!!



infinity
05-01-2009, 12:04 PM
I have attached a sample workbook. This has got me stumped :banghead: . When you press the "Update Bill" button it is supposed to pull up the UserForm "New_Bill_Received" and wait for a selection from the user, this works fine. Once the user selects what they want to update it should take the "Last Payment" data and put it in "Payment History". If you step through the code, once it gets to the point that should be setting the RowHeight on Payment History it jumps to the Private Sub Pmt_Creditor_Name. I cannot figure out why :dunno .

In order to avoid VBA Tag overload on this thread, in the code in the attched workbook I have put comments that begin with 5 asterisks and are numbered sequetially - (1), (2), (3), (4a), (4b), (5a), (5b), (6) and (7) explaining what it is doing and what it should be doing. I think these comments will explain my delemna. If not I can explain further. I know this is something with my code but I am tearing my hair out trying to figure out what I am doing wrong, I have a full head of hair right now and would like to keep it that way. As always, thank you for your help!!!

Bob Phillips
05-01-2009, 12:22 PM
I get extremely confused by which is the activecell with all of that sselecting. Is it really meant to offset from a continually changing cell?

infinity
05-01-2009, 12:25 PM
Yes, the reason for that is because once the user selects the bill they want to update it needs to perform actions based on that selection. I do not know any other way of doing it.

infinity
05-02-2009, 03:05 PM
Does anyone have any suggestions on this one? Are my explainations in the comments sufficient? Thank you!

p45cal
05-03-2009, 01:49 PM
I've taken a glance at this and the first thing I noticed that may not be working as you expect, is the value of a variable called bresponse. You've DIMed it in several modules where its scope is limited to that module. As the code moves from module to module, bresponse is not the version/value I think you think it is. Consider declaring it once at the top of any standard module as Public, and nowhere else. Alternatively you could refer to the version you want with the likes of New_Bill_Received.bresponse and Add_Bill.bresponse. This latter wouldn't be my preferred approach.

Also, I note that you have the following code somewhere:
Unload Add_Bill
Add_Bill.Hide Note that the second line is superfluous, and in fact reinitialises the userform. I suggest removing it, unloading causes the userform to disappear.

I suspect once these are done you may find less unexpected behaviour - post again if it doesn't - I'm tired now.

p45cal

infinity
05-03-2009, 08:13 PM
Okay, you are awesome!!! I would have never guessed. I thought I had to use .Hide if I wanted to retain the value of bResponse but I am guessing that it was retaining that value even when I didn't want it too, right? It appears to be working now. Thank you soooooooo much! I have had it working before and then all of a sudden it would blow up on me and once it did I could not get it working again, I have tested it about 10 times and all looks good so far, hopefully it will not blow up again. THANK YOU, THANK YOU! :clap: :friends:

p45cal
05-04-2009, 07:31 AM
but I am guessing that it was retaining that value even when I didn't want it too, right? I dont think so, unloading doesn't retain anything - I think you're right that you should use .hide to retain values, it's just that you had more than one version of bresponse, and it didn't contain the value you expected. Normally, you'd use .hide first on a modal form to allow code execution to continue, then when all values associated with that form/form module are finished with you can .unload it and clear some memory.

infinity
05-05-2009, 10:22 PM
Normally, you'd use .hide first on a modal form to allow code execution to continue, then when all values associated with that form/form module are finished with you can .unload it and clear some memory.

I am having the same problem with this. When the UserForm is showing the user can enter values into any of the four TextBoxes on the form which will be entered into cells on the worksheet when the OK button is pressed. On the Private Sub OK_Button_Click() I am trying to use .Hide so that the values entered in the TextBoxes is retained and then I Unload the userform at the end of the normal module but it is doing the same thing as before, I am not sure what I am doing wrong. Can you give me advice?

p45cal
05-06-2009, 11:41 AM
current workbook /code?

infinity
05-06-2009, 12:10 PM
I suppose that would have been helpful :doh:. Here it is, thank you!

p45cal
05-06-2009, 03:40 PM
put
Public bResponse As Boolean
at the top of Bill_EnterUpdated code module and remove any other
Dim bResponse anywhere else.

Follow your own comments here (I've done it):Private Sub OK_Button_Click()
bResponse = 2
' this is where I need to use .Hide so that it retains the values entered by the user, the form
' will be unloaded at the end of Sub Update_Bill()
' Unload New_Bill_Received
New_Bill_Received.Hide
End Sub
(I note that you've assigned 2 to the variable when it's boolean?! which I think results in it being true)
and reinstate the the commented-out 'Unload New_Bill_Received' here, in Sub Update_Bill():If bResponse Then
Unload New_Bill_Received
Exit Sub then put a break (or multiple breaks)in the code, especially one at the beginning of the macros assigned to the buttons, then step through the code using F8, line by line and watch the value of bresponse and other variables by either hovering over them with the mouse or bring up the Locals pane with Alt + v, then S, or even add them to the Watch pane. You'll soon spot when a variable isn't what you'd thought it to be.
It's not clear without a fair bit of effort on my part to deduce what you're intending it to do, so I'll wait for feedback.

infinity
05-13-2009, 09:21 AM
P45cal,

I am so sorry I have not responded until now. All my email normally goes to my BlackBerry and this one did not go through so I did not even know until today that you had posted a reply, thank you, I am looking at this now and will respond back.