Excel VBA 2010 - Programming problems with table interface

[b4tmast]

First, I am totally new to VBA. I submitted a problem here before trying to get a UserForm to work. Someone was gracious enough to help out. I decided that since the UserForm 'floats', that it really wouldn't work for my needs. So I've tried to adapt the UserForm to a regular spreadsheet. I am having multiple problems: First, I can't get the First, Last, Next and Prevoius buttons to work. Completely lost as to what I've done wrong. Second, I can't get the Command Buttons to work properly. They should by Default be 'Save' & 'New', which allows a person to modify an existing record which would change the New button to Cancel. They would also allow the user to 'Add' a new record. Again, I can't get these to work. Any help on the coding I've gone astray with would be immeasurably helpful. My Thanks in advance.

[jaslake]

Hi b4tmast

If you change this Line of Code your UserForm will not "Float".

You then can then, with Code, position the UserForm on the screen as you require.

From

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

[b4tmast]

Thanks for the suggestion. I appreciate your time and effort. The omission of the vbmodeless, locks the entire workbook/sheet etc. It doesn't help with my needs as I mentioned above. It prevents people from moving around as long as the 'Userform' is open. I am trying to make a regular page act as the Userform so that it doesn't have to be opened or closed and that way they can maneuver around the various pages. I've solved the issue with the First, Next, Previous and Last Issues. My problem now is that I can't figure out how to make it so that when the data is showing, I can edit it and save it. The Combobox that everything is dependent upon, seems to lock it up. As well as the other fields. Any idea what I'm doing wrong? Thanks again for your help and time. Please see the revised spreadsheet with the working First, Last, Next, & Previous buttons. Notice that the Save & Add/New Command buttons don't work - my problem...

[vichopalacios]

hi @b4tmast

I was checking your code, and found some little problems, so
I've changed a bit here and there. I didn't have time to finish, but it's on its way
Your Names combobox, is making some difficulties, and I haven't solved the input for a new record yet.
hope I have time this weekend.

please check how it is going for now.

[b4tmast]

Vichopalacios - Thanks for taking the time. I've looked at what you've added, some I understand, some I don't. I will check into it as well. I experience the same problem. For some reason, when editing a record, it doesn't record the new changes if they are made from the combobox. Changes from the other fields, get updated - except the state field array doesn't always show. I've played with Combobox Styles, etc. and still have the same problem. I'm hoping that that is what is affecting trying to add new records as well. When I go to add a new Record, the first letter gets printed in the combobox, from there, it resumes the typing in A1 or sometimes A2??? Looking forward to your input and thank you very much for your help so far.

[b4tmast]

Duh, I see why the state array is not working....

[vichopalacios]

I know where the problem arises from,
It's because you are using the cmbname_change event.
I thought you need it to use the functionality it gives when you type the first letter, you get the first coincidence.
We can use a little trick to bypass that problem, not sure if you find it adecuate.
let me have some time free at my desk to code it for you.
If you need to understand any part of the codes, just ask here or use a P.M.

I forgot to tell you to check the "Thisworkbook" module

[vichopalacios]

Here is the file

[b4tmast]

Fantastic! The 'New' works now! I have to bring back the First, Last, Next & Previous buttons after 'Save'. It will take me a little while. Working on a ship right now, need to navigate; with limited access to the computer and satellite. I will play with it. I definitely have a question or two for you. Please be patient. I thank you for this. Give me a day or so... Immeasurable thanks.

[vichopalacios]

After "Save" your code goes into "Edit_Record Mode"; if user need to input a New Record, just press "New" or, if you prefer, force New_Record Mode" after saving.
Perhaps you should think about a "Delete Record" option, which for now is impossible.

[b4tmast]

First and foremost, thank you. Your time, generosity and input are truly appreciated and valued. I looked at your code and loved it! Do have some questions/observations:
*What is the difference between the cmbName_LostFocus and cmbName_Change? (I need to be able to see what each property means. I don't understand them yet. I need to obtain a reference list.)

*After I did a Save, none of the 'arrows' were Enabled, so I called your CheckEnabled subroutine (which I loved, and is at the crux of this applet) after the Sort command at the end of the cbSave. Works good! Any comments/suggestions?

*Could you please help me understand how this works?

Please Login or Register  to view this content.

I understand that lblRowNumber must be a number, If it is not numeric, then lUnDoChange? .Caption - That continues the value of lblRowNumber conditional, Then. Correct?
What does it mean and how does it work: Application.Max (and Min)(CLng(.Caption),2 and last row work?

* Why add Application.ScreenUpdating = False, Wouldn't we just be updating the table/sheet?
Why ScreenUpdating?

Just curious, why now would it be impossible to 'Delete Record'? I was thinking rather than utilizing the cells on the sheet, maybe I should just make them all txtboxes so that the user can tab from box to box?

Another thought, is there a way to limit what gets shown in the cmbName dropbox? For example, maybe I only want the dropbox to show TX names? is there a way to limit what gets shown?

Again. thank you very much for you time and input. Truly appreciated.

[b4tmast]

Here you go...

[vichopalacios]

Hi @b4tmast

*What is the difference between the cmbName_LostFocus and cmbName_Change?

When _change is used, as soon as you type the first letter into the box, the code runs....
when you use _lostFocus the code does not run until you leaves ( goes out) the box, so you can type until the end of the text, and moves out of the box.

*After I did a Save, none of the 'arrows' were Enabled, so I called your CheckEnabled subroutine (which I loved, and is at the crux of this applet)
after the Sort command at the end of the cbSave. Works good! Any comments/suggestions?

when you write a code for a "complex work" the best idea is to construct in a modular way...
after that you just call modules, and combine them according to your needs... play with them, it's the best way to learn....
let me tell you, I never went to a school to learn computing, nor excel, nor anything alike.....I am a self-taught amateur excel user, that learned a lot in sites like this forum.

*Could you please help me understand how this works?

I just forgot to delete it... it was in your original code, and in the place where it is, is absolutely useless.
.... nobody can input data in a label...
In fact it intends to control the input into the label "Me.lblRowNumber"..... (assuming somebody will do it)...
.- controling this data must be a number
if its is not, then use the " lUnDoChange " variable to put again the previous value.
.- controling that this number be at least 2 using function MAX between two values:
the caption and 2, so if you write 1 or lower, the function chooses 2, if you write greater than 2, the function chooses the caption.
.- controling the number is at most Lr...using the MIN function between caption and Lr.

* Why add Application.ScreenUpdating = False,

In the Sort subroutine, you are "selecting" and Activating Cells and Areas.....
when you run the code, you will see the screen Blinking as the selected areas are moving around...
it is a good costume to set screen updating to FALSE at the begining of the code, avoiding the blinking,
and set updating to TRUE at the end of the code just to show the final result. this saves time and memory.

It is a better idea NOT to use slect unless it is absolutly necessary.

Another thought, is there a way to limit what gets shown in the cmbName dropbox?
For example, maybe I only want the dropbox to show TX names?
is there a way to limit what gets shown?

I don't get your point, I'm sorry...but for sure the answer is a resounding YES... you can do almost whatever you want with the list in your combo.
I don't have in mind the max number of items in the combo list, but it is easy to find.

Another point...
I don't like at all the way of populating the form using the function VLOKUP, I do prefer a bucle around the specific part of the table.

If you have any other point, come back

Cheers

[b4tmast]

Vichopalacios,
First my apologies for not coming back sooner. As an explanation, not an excuse, I work on ships and for the last week we were shuttling back and forth non-stop between ports. No time to access the computer. My apologies. I understand the need for promptness on these sites when someone is giving their time and effort to helping another person out. My humble apologies. I read what you wrote above which triggers some more questions, but, first, let me explain what I did to the spreadsheet:
1. When using the First, Next, Last and Previous buttons, when on the last row, it wouldn't disable the First & Previous buttons. So I added the following code:

Please Login or Register  to view this content.

That seemed to work so the arrows work properly now.

I also changed the spreadsheet cells to txtboxes. Working on a way for the end user to be able to tab from one box to another.

The change that we did; cmbName_Change to cmbName_LostFocus, creates another problem. When the user uses the Dropbox to quickly find another record, it doesn't update the other fields until they 'LostFocus' and move to the next txtBox - kind of defeats the purpose of having the dropbox there. Any suggestions? When I try to think how to solve this logically, it eludes me. On one hand, if we use cmbName_Change, then we are right back where we were at the beginning without the editing or adding capabilities. Is there a way to have a conditional If, Then, incorporating both? Like using the cmbName_Change for one condition and the cmbName_LostFocus for another???

Your last sentence has me concerned. If VLOOUP is a poor choice, then what method would you use? What would be the 'best practices' method populating the other fields?

Thanks again for your help and patience. BTW, hopefully will be sporadically available for the several days. M

[b4tmast]

Here's what I did ...