The problem is that when it takes 20 seconds to load and fire your gun, and your gun has an effective range of 100m or less, you get one shot to stop infantry or cavalry closing with you. Tactics like volley fire can help, but it's still problematic. The problem is made worse by armour that can stop the bullets.

It's the same problem faced by archers and crossbowmen: it's bad news when enemy cavalry gets in close with you. What enabled the great English "longbow victories" (which perhaps owed rather less to the longbow than myth would ascribe) was that the archers were protected, by field fortifications or a screen of armoured infantry. Where these measured were not implemented (e.g., Bannockburn), the English archers could be, and sometimes were, defeated easily, sometimes followed by the rest of the English army. Compared to archers, handguns provided better performance against armour, but lower rates of fire. Worse, the better performance against armour was not reliable - there were bulletproof breastplates on the battlefield (just as there were arrow-proof and crossbow-proof breastplates). The gun suffered from the same vulnerability.

This vulnerability was worst against cavalry, because they could close that distance most quickly. Cavalry, where they were rich elite of the battlefield (as in Medieval western Europe), were often well armoured. very bad news to let them close with you. Such cavalry were often armed with lances. Infantry could also be dangerous: as late as 1661, there were demonstrations of the vulnerability of musketeers to determined and well-armoured forces - in that year, the Ming loyalist Zheng Chenggong (AKA Koxinga) defeated the Dutch on Taiwan, using armoured infantry to steamroller the force of Dutch musketeers sent to stop them.

Solution: have infantry with pikes (which will outreach cavalry lances) to protect archers, or crossbowmen, or handgunners, from enemy cavalry and infantry. The pike was also an effective offensive weapon. The offensive role of the pike, and the role of the pike in combined arms warfare of the time was discussed by /u/Iphikrates and /u/BeondTheGrave in https://www.reddit.com/r/AskHistorians/comments/guzxwg/im_attached_to_mercenary_swiss_pike_company_in/

Four things came together that led to the end of pikemen. First, improvements in guns meant that bullet-proof armour was largely a thing of the past. Even the best-armoured cavalry became vulnerable to musketeers. This made musketeers better able to stop the enemy from closing with them. Second, improved tactics such as volley fire allowed sustained fire against enemy trying to close with musketeers. Third, the bayonet. The bayonet, widely adopted in Europe at the end of the 17th century, allowed musketeers to be part-time pikemen. Fourth, the economics and logistics of warfare meant that it wasn't feasible to have a bunch of extra soldiers with pikes along for the ride. Opposing forces could be expected to be approximately equal in armament and size, and it was better to put the limited resources available for paying and feeding one's army into the most effective-for-the-cost forces, i.e., musketeers rather than pikemen.

All of this took time, many centuries. It was over 300 years from the introduction of the gun in the West before the pike largely disappeared from the battlefield. For much of that time, the gun accompanied, rather than replacing, the bow and the crossbow. As the gun improved, it replaced the bow and crossbow, and relatively shortly afterwards, resulted in the abandonment of most armour from the battlefield. Finally, the bayonet allowed the musketeer to reign supreme. That is, apart from cavalry that could still seriously stomp them if they were not is suitable formation to use their bayonets as pike substitutes, and artillery that could blast them while they stood ready to fend off cavalry (not that pikemen would have been a panacea against artillery).